SUMPRODUCT - Tweak

[Sam via OfficeKB.com]

Hi All,

I am using the following SUMPRODUCT Formula:

=SUMPRODUCT(--(Data=InputCell_DB)*(SUBTOTAL(103,OFFSET(INDEX(Data,1,1),ROW
(Data)-MIN(ROW(Data)),InputCell-1,,))))

to return a summed count of a varying criteria; row 1 = the formula, row 2 =
text, row 3 = numeric values, row 4 = start of range "Data". The range
"Data" is dynamic with one column and many rows. It holds numeric values and
non-numeric data.

"Data" is defined as:
=OFFSET(Quarterly!$BS$4,0,0,COUNTA(Quarterly!$BS:$BS)-3,1).

Although "Data" is defined as a single column, I use the OFFSET Function to
access other columns adjacent to "Data".

With data in rows 1-3, above my dynamic range "Data" (start row 4), the
SUMPRODUCT Formula is returning a #VALUE! error. I've also tried defining the
range using the INDEX Function to hard code the range starting at row 4 and
avoiding COUNTA so that the entire column is not accessed; however, I still
get a #VALUE! error.

Can the above SUMPRODUCT Formula be amended to ignore rows 1-3?

Thanks,
Sam

 

[Domenic]

Try...

=SUMPRODUCT(--(Data=InputCell_DB),SUBTOTAL(3,OFFSET(Data,ROW(Data)-MIN(RO
W(Data)),InputCell-1,1)))

Hope this helps!

 

[Sam via OfficeKB.com]

Hi Domenic,

Thank you very much for your assistance. It worked a treat!

=SUMPRODUCT(--(Data=InputCell_DB),SUBTOTAL(3,OFFSET(Data,ROW(Data)-MIN(RO
W(Data)),InputCell-1,1)))

I also tried again, the original posted version that gave me #VALUE! error;
I must have had a typo in the Formula on my worksheet because it now returns
the correct value! No error.
I think I had extra parenthesis in my worksheet version.

=SUMPRODUCT(--(Data=InputCell_DB)*(SUBTOTAL(103,OFFSET(INDEX(Data,1,1),ROW
(Data)-MIN(ROW(Data)),InputCell-1,,))))

Thanks again.

Cheers,
Sam