SUMPRODUCT

[Chad Portman]

I am using excel 2003 and having issues with this formula. What I need is a
count of how many rows contain both condition one from column A and condition
two from column b. The formula I have right now is:

=SUMPRODUCT(--(A1:A5000=C1),--(B1:B5000=C2)

C1 and C2 are my two conditions. Now sometimes I can easily just auto filter
the data and count it and then manually type it but sometimes it is too large
to do this. The reason I mention this is in one case once filtered there is
only 5 rows that match both conditions but the formula returns 518. So I am
wondering if this is the right formula to find what I am truly looking for
cause it sounds to me that I almost need a couple COUNTIF's with a & in the
middle but I can not get that to work either. Thanks for any help

 

[Luke M]

You are on the right track, you may need to force the formula to multiply the
arrays. (I'm assuming you accidentally left off a parenthesis, as your
formula you posted is not valid)
Try:
=SUMPRODUCT((A1:A5000=C1)*(B1:B5000=C2))

 

[Elkar]

Your formula is correct (other than the missing parenthesis at the end).

One reason you may see a difference between the formula and the AutoFilter,
is if you have numbers stored as text. SUMPRODUCT will consider the number
123 different than the text string "123". While AutoFilter will treat them
the same.

Another thing to watch out for when using AutoFilter, is if you also have
Freeze Panes activated. The filtered results won't always be scrolled to the
top of the list. So, when the filter is applied, you may only see 5 rows
visible, but more will be revealed if you scroll up.

HTH
Elkar

 

[Chad Portman]

Luke M you are my hero. I love you so much right now.

 

[Pete_UK]

If you are searching for text values and one of those columns (eg B)
could contain blanks, then you might have to do this:

=SUMPRODUCT(--(A1:A5000=C1),--(B1:B5000=C2),--(B1:B5000<>""))

Hope this helps.

Pete