Sumproduct Subtotal #Value! error

[Graham]

Hi

I have a formula that works

=IF(Insurer!C2="Total",
COUNTIF(DATA!J2:J61546,"c"),
SUMPRODUCT((DATA!$D$1:$D$61546=Insurer!C2)*(DATA!$J$1:$J$61546="c")))

But I have now introduced a filter to the data sheet and I want to use
SUBTOTAL to evaluate the visible records.

The formula works where 'Insurer!C2="Total"' but returns a #VALUE! error
otherwise

=IF(Insurer!C2="Total"
SUMPRODUCT(SUBTOTAL(103,OFFSET(DATA!$A$2:$A$64999,ROW(DATA!$A$2:$A$64999)-MIN(ROW(DATA!$A$2:$A$64999)),,1)),
--(DATA!$J$2:$J$64999="C"))
SUMPRODUCT(SUBTOTAL(103,OFFSET(DATA!$A$2:$A$64999,ROW(DATA!$A$2:$A$64999)-MIN(ROW(DATA!$A$2:$A$64999)),,1)),
--(DATA!$J$2:$J$64999="C"),--(DATA!$D$1:$D$64999=Insurer!C2)))

Any help is appreciated

Graham

 

[Luke M]

Your last array is 1 row larger than all the rest (calls out D164999).
Change it to
=IF(Insurer!C2="Total"
SUMPRODUCT(SUBTOTAL(103,OFFSET(DATA!$A$2:$A$64999,ROW(DATA!$A$2:$A$64999)-MIN(ROW(DATA!$A$2:$A$64999)),,1)),
--(DATA!$J$2:$J$64999="C"))
SUMPRODUCT(SUBTOTAL(103,OFFSET(DATA!$A$2:$A$64999,ROW(DATA!$A$2:$A$64999)-MIN(ROW(DATA!$A$2:$A$64999)),,1)),
--(DATA!$J$2:$J$64999="C"),--(DATA!$D$2:$D$64999=Insurer!C2)))

 

[Graham]

DOH!

Thanks for your help Luke
All works fine now

Graham