• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

Excel - countif function with an additional condition

I have a spread sheet with the following columns:

A - City Truck is in
B - Qts of oil needed
C - Oil allocated?

I have a report built into a spreadsheet that totals all of the qts of oil needed by City. I did this using the countif function. countif(A10:A1000,A1,B10:B1000). This works nicely but what I'd like to do next is put an "X" or "yes" in the C column when the oil is allotcated and have another cell in the report that shows how much oil is not allocated yet. In other words, I want to do the same countif function but this time not include the rows that a value in the C column.
0
LN41
Asked:
LN41
  • 3
  • 3
  • 3
1 Solution
 
gowflowCommented:
if it is in D that you want to put the countif you put this
=COUNTIF(C1:C1000,"<>X")
gowflow
0
 
barry houdiniCommented:
>I have a report built into a spreadsheet that totals all of the qts of oil needed by City. I did this using the countif function. countif(A10:A1000,A1,B10:B1000)

that isn't a valid formula - I assume you are using SUMIF, i.e.

=SUMIF(A10:A1000,A1,B10:B1000)

To add another condition you can use SUMIFS (with an "S" on the end) if you are using Excel 2007 or later, i.e. this to sum column B when A matches A1 and C is blank

=SUMIFS(B10:B1000,A10:A1000,A1,C10:C1000,"")

In earlier versions use SUMPRODUCT

=SUMPRODUCT(B10:B1000,(A10:A1000=A1)*(C10:C1000=""))

regards, barry
0
 
LN41Author Commented:
The image included probably better explains. I'm able to get the totals by city but what I want to do is have a column in the report that will show total qts not allocated. In this case, Bakersfield should show 400, Las vegas 154, and Denver 300. I'm trying to figure out how to get the total qts needed minus the qts allocated.
xcl.png
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
barry houdiniCommented:
You can use a version of the formulas I suggested, adjusted for the layout shown i.e. with this formula in C16 copied down

=SUMPRODUCT(B$4:B$11,(A$4:A$11=A16)*(C$4:C$11=""))

or better in Excel 2007 and later with SUMIFS

=SUMIFS(B$4:B$11,A$4:A$11,A16,C$4:C$11,"")

regards, barry
0
 
gowflowCommented:
Barry
SUMPRODUCT in EXcel 2007 won't give result. SUMIFS is perfect

LN41
In your example given indeed you will get with barry's formula Bakersfield should show 400, Las vegas 154, but Denver is 350 + 90 = 440 and not 300 as you pointed out.

gowflow
0
 
LN41Author Commented:
Barry, you are correct - I was using "sumif", not countif - my bad.

The formula provided looks to work perfectly. I'm just stumped as to why it won't work for the cell in C16. Its the same formula as C17 and C18 which work as expected. I tried the same formula in other cells - same result. Anything you can think of why it's not working only for Bakersfield?  I'm awarding points as it is working for the other two cities.
xcl2.png
0
 
barry houdiniCommented:
Did you accept the wrong answer by mistake?

If C16 doesn't work then perhaps you have a space or some other "non-printing character" in cells C7 and C8 - If you are going to use "yes" consistently in column C then perhaps better to check explicitly that the value isn't yes, i.e. like this

=SUMPRODUCT(B$4:B$11,(A$4:A$11=A16)*(C$4:C$11<>"yes"))

regards, barry
0
 
gowflowCommented:
because your bakersfield in 7 and 8 contain probably a space and line 6 doesn't or vice versa
like 'Bakersfield '

I am surprised to why you awarded me the points when Barry gave you the answer !!! you must hv checked the wrong answer !!! ?
gowflow
0
 
LN41Author Commented:
Yep - I didn't realize that another comment came in and accepted the wrong answer. Points awarded to Barry. Thanks for the participation, gowflow.

I thought maybe whitespace or some other formatting might be the issue but not to worry,it was only for example purposes. So far I have started using the formula in my production copy and it's working just fine. Again, thanks for the quick responses and excellent help!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now