Solved

# Excel - countif function with an additional condition

Posted on 2011-10-08
202 Views
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
Question by:LN41

LVL 29

Expert Comment

if it is in D that you want to put the countif you put this
=COUNTIF(C1:C1000,"<>X")
gowflow
0

LVL 50

Expert Comment

>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

LVL 1

Author Comment

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

LVL 50

Expert Comment

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

LVL 29

Expert Comment

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

LVL 1

Author Comment

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

LVL 50

Accepted Solution

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

LVL 29

Expert Comment

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

LVL 1

Author Comment

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

### Suggested Solutions

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.