SUMPRODUCT with SUBTOTAL not adding up

[milworker]

This is off by five, but only when I'm looking for zero (0). There are no blank cells in the AV column, other than actually empty cells. The count is also off when I turn all filters off...

Thoughts?

=SUMPRODUCT(SUBTOTAL(2,OFFSET(A17,ROW(AV:AV)-ROW(A17),0)),--(AV:AV=0))

 

[milworker]

Oh, and every other formula, subtotal, sum, countif, whatever, on the page works perfectly. Just this one row, and it's driving me batty because it needs to be in a report...

Oh, and the four cells above this one all total correctly, see below:

=SUMPRODUCT(SUBTOTAL(2,OFFSET(A15,ROW(AV:AV)-ROW(A15),0)),--(AV:AV=4))
=SUMPRODUCT(SUBTOTAL(2,OFFSET(A16,ROW(AV:AV)-ROW(A16),0)),--(AV:AV=3))
=SUMPRODUCT(SUBTOTAL(2,OFFSET(A17,ROW(AV:AV)-ROW(A17),0)),--(AV:AV=2))
=SUMPRODUCT(SUBTOTAL(2,OFFSET(A18,ROW(AV:AV)-ROW(A18),0)),--(AV:AV=1))

No rows are hidden, count of rows is 625, but the 0 row kicks back five more than it should.

Thanks in advance,

Rob

 

[milworker]

Ok, I reduced the range and now it adds up...

=SUMPRODUCT(SUBTOTAL(2,OFFSET(A17,ROW(AV22:AV700)-ROW(A17),0)),--(AV22:AV700=0))

Nevermind that I deleted every row down past 10,000 and that all the other rows total up just fine using the AV:AV method. This makes NO SENSE.... thank you Excel... ARRRGH!!!