Sumif problem

[rjmat]

Hello,
I am puzzled and cannot figure out what the problem is. It is a simple sumproduct formula (I use pretty frequent). In this case, it is not producing the right result.

I have 8,000 rows of data. I used a subtotal function on the top in row 1. The filter is active with headings on row 2. So far so good. Now, when I filter (AJ) a column to show only 0 (zero), another column (=SUBTOTAL(9,AC3:AC7804)) reflects a value (171). Now, I want to use a sumproduct formula (=SUMPRODUCT(--(AJ3:AJ7804=0)*(AC3:AC7804))) to produce the same result with the use of a filter. It came back with another value (225). I have no idea why? It should be showing 171.

Any help is appreciated
RJMAT

 

[taurean]

Its because the second array multiplies with the result and gives you the sum of it. i.e. Have a quick look at formula evaluation toolbar on small sample of say 5, 10 values. If you are interested in getting the count then you should use either:
=SUMPRODUCT(--(AJ3:AJ7804=0))
OR
=COUNTIF(AJ3:AJ7804,0)

 

[Andrew Poulsom]

Here's an example:

http://www.mrexcel.com/forum/showthread.php?t=391084

 

[rjmat]

taurean - thanks for your efforts, however, it only functioned like a count. Not exactly what I was looking for.

Andrew Poulsom - thank you very much, that did the trick! It is amazing how this formula works with the addition of subtotal function.

Have a great day to both of you!

 

[taurean]

I misunderstood your requirement and then learned something on the way.