SUMPRODUCT/COUNTIF???

[dwake]

So here is my situation:

A B C
Pilot Machine FY2009-Q4
Proto CEM FY2010-Q1
Pilot Commercial FY2010-Q2
Proto Machine FY2010-Q3

I'm having problem counting with multiple criteria. I want to add up all
the rows that meet certain criteria. So if the data meets the criteria of
pilot, machine, and FY2009-Q4, I would like it to count all those rows that
meet that criteria to report on how many are happening in fourth quarter of
2009.

 

[Peo Sjoblom]

=SUMPRODUCT(--(A2:A100="Pilot"),--(B2:B100="Machine"),--(C2:C100="FY2009-Q4"))


Change ranges to fit your data

If you have multiple criteria to check then I would use cell references
instead of the hard coded criteria like


=SUMPRODUCT(--(A2:A100=D2),--(B2:B100=E2),--(C2:C100=F2))


as an example, the type the criteria in those cells. That way you don't have
to change the original formula only the criteria cells contents

--


Regards,


Peo Sjoblom

 

[David Biddulph]

=SUMPRODUCT(--(A2:A5="Pilot"),--(B2:B5="Machine"),--(C2:C5="FY2009-Q4"))

 

[Eduardo]

Hi,
try

=SUMPRODUCT(($A$1:$A$4="Pilot Machine")*($B$1:$B$4="FY2009")*($C$1:$C$4="Q4"))

if this helps please click yes thanks

 

[dwake]

The formula result is returning zero and not counting the rows of data?

 

[David Biddulph]

Check that you haven't got spaces, non-breaking spaces, or other
non-printing characters in any of your cells.

If row2 is a row that should be counted, check that =LEN(A2) gives 5,
=LEN(B2) gives 7, and =LEN(C2) gives 9.
If you are using Peo's 2nd formula, similarly check that =LEN(D2), =LEN(E2),
and =LEN(F2) return 5, 7, and 9 respectively.

You can also check the criteria from Peo's 2nd formula individually and see
whether =A2=D2, =B2=E2, and =C2=F2 all return TRUE.