S
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
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