COUNTIF with more than one criteria

[sbitaxi]

I have a workbook comprised of several worksheets, each worksheet
represents one of our organizations projects, the first worksheet
summarizes all the information on each of them.

Each column of the summary worksheet has a countif formula that uses
one column/field in each of the other worksheets, so that has been
easy.

I now have a new worksheet where I will have to COUNTIF based on two
fields

e.g.
A B
1 Type Region
2 Apple North
3 Orange South
4 Pear South
5 Grape West
6 Apple North
7 Apple East
8 Bannana South

I want to count the number of Apples in the North Region without
creating an extra calculation column. Is this possible?

 

[Guest]

Hi

One option is to use SUMPRODUCT. Have a look here for more information and
post back if it's not enough!!

http://www.contextures.com/xlFunctions01.html#SumProduct

Andy.

 

[sbitaxi]

It's close, I'll spend some time with this and let you know if it
helps. It seems to rely heavily on number results, whereas my array
only contains text values that I want to count.

Thanks for the suggestion. I have never used SUMPRODUCT, so I am
learning something new regardless.

S

 

[Guest]

Hi

Try something like:
=SUMPRODUCT(--(A2:A15="Apples")*(B2:B15="North"))
When using this function, the ranges must contain the same number of cells
and cannot be whole columns.

Andy.

 

[Bob Phillips]

Why mix operators, either go

=SUMPRODUCT(--(A2:A15="Apples"),--(B2:B15="North"))

or

=SUMPRODUCT((A2:A15="Apples")*(B2:B15="North"))



--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Just covering all bases!! As long as it works . . .

Andy.

 

[Bob Phillips]

Just covering all bases!! As long as it works . . .

Try telling Harlan Grove that <vbg>

 

[Guest]

LOL

I know what you mean!!! Thanks for making me smile!!

Andy.

 

[sbitaxi]

That worked. I used it for the wrong array initially and kept getting 0
as the result, but once I figured that out Excel gave me the pretty
little number I was looking for.

Thanks again!



S

 

[Steven Bitaxi]

Hey Andy/Bob

The formula worked beautifully. I am trying to figure out how to work
this one on a 3 part now.

The way I used the formula is as follows

=SUMPRODUCT((A2:A15="Apples")*­(B2:B15=B1))

Now, what I would like to do is compare a range of cells like in the
above formula, then ADD/SUM the values with both those criteria from a
third column.

I have a summary worksheet (SmryWS in the following formula) and a data
worksheet (OtherWS in the formula)

Summary
A B C D
1 Fruit Paris London Berlin
2 Apples 20 5 600
3 Oranges 64 454 9
4 Grapes 6000 22 190


Data
A B C
Type of fruit City that ordered Quantity



I want to total the quanity for each fruit by the city

=SUMPRODUCT((OtherWS!A2:A15=SmryWS!A2)*(B2:B15=SmryWS!B1)*(OtherWS!C2:C15)

I know the formula is not quite right, and I may even be using the
wrong function. Am I on the right track? Any recommendations?


S