Sumproduct Subtotal #Value! error
 

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

Sumproduct Subtotal #Value! error
 

Your last array is 1 row larger than all the rest (calls out D1:D64999).
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)))

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Graham" wrote:

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

Sumproduct Subtotal #Value! error
 

DOH!

Thanks for your help Luke
All works fine now

Graham

"Luke M" wrote:

Your last array is 1 row larger than all the rest (calls out D1:D64999).
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)))

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Graham" wrote:

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