Countif? or Vlookup? Lost??

[Guest]

Hi Guy's,
here is the problem:

I have 3 criteria that must be met then the last one to be summed. e.g

Column 1 Column 2 Column 3
North PSR Yes
South ASR No
North PSR Yes
West ASR Yes

The criteria is: find all the "north" then all the "PSR" the count the "yes"
only if they match the first 2. So essentially this should count 2. If i had
North further down column 1 it should then count 2 if they were PSR and YES
matched. Its kind of a filter i suppose, but they only want to display the
results "for managers" (so it has to be easy....lol).
Essentially others will be filling in the data and I have a consolidation
sheet at the front for each area.
I tried an if statement with countif but didn't produce the results.

Your help would be appreciated, it's a pretty complex ss with about 15
sheets and these are the last formulas i need to get these managers off my
back.

Thanks heaps.
Leo

 

[John Michl]

Leo,

=SUMPRODUCT((A2:A5="North")*(B2:B5="PSR")*(C2:C5="Yes"))

- John
www.JohnMichl.com

 

[Guest]

John,
your an absolute legend!!!
I had sumproduct((******),--(*******),--(*********)) and it was only
counting the first range criteria, got this off reading other posts but
could't quite crack it.

Thanks very much
Leo

 

[John Michl]

Leo,
Check out http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a
pretty good review of the ins and outs of SumProduct.

- John