How to use subtotal in sumproduct with multiple criteria

**Daniel Tou** · 05-10-2017, 04:53 PM

I have the function below and i need to apply subtotal to it, so that the function could show the result when data is filtered.

=((SUMPRODUCT((K2:K37409<>"")*(O2:O37409>0)))+
(SUMPRODUCT((K2:K37409<>"")*(O2:O37409=0)*(J2:J37409<>"NU")))+
(SUMPRODUCT((K2:K37409<>"")*(I2:I37409="TAX PAYMENTS")*(AN2:AN37409>0))))/
((SUMPRODUCT((K2:K37409<>"")*(AL2:AL37409="Activ")))-
(SUMPRODUCT((K2:K37409<>"")*(AL2:AL37409="Activ")*(G2:G37409="PJ_CLOSED")))-
(SUMPRODUCT((K2:K37409<>"")*(AL2:AL37409="Activ")*(AP2:AP37409<>"")))-
(SUMPRODUCT((K2:K37409<>"")*(AL2:AL37409="Activ")*(AM2:AM37409<>""))))

Please help me with a solution ,i tried the function below but it says that i need to pass more arguments :

=((SUMPRODUCT(SUBTOTAL(3,OFFSET(K2:K37409)-ROW(K2),0)),(K2:K37409<>"")*(O2:O37409>0)))+
(SUMPRODUCT(SUBTOTAL(3,OFFSET(K2:K37409)-ROW(K2),0)),(K2:K37409<>"")*(O2:O37409=0)*(J2:J37409<>"NU")))+
(SUMPRODUCT(SUBTOTAL(3,OFFSET(K2:K37409)-ROW(K2),0)),(K2:K37409<>"")*(I2:I37409="TAXPAYMENTS")*(AN2:AN37409>0))))/
((SUMPRODUCT(SUBTOTAL(3,OFFSET(K2:K37409)-ROW(K2),0)),(K2:K37409<>"")*(AL2:AL37409="Activ")))-
(SUMPRODUCT(SUBTOTAL(3,OFFSET(K2:K37409)-ROW(K2),0)),(K2:K37409<>"")*(AL2:AL37409="Activ")*(G2:G37409="PJ_CLOSED")))-
(SUMPRODUCT(SUBTOTAL(3,OFFSET(K2:K37409)-ROW(K2),0)),(K2:K37409<>"")*(AL2:AL37409="Activ")*(AP2:AP37409<>"")))-
(SUMPRODUCT(SUBTOTAL(3,OFFSET(K2:K37409)-ROW(K2),0)),(K2:K37409<>"")*(AL2:AL37409="Activ")*(AM2:AM37409<>""))))

**Richard Buttrey** · 05-10-2017, 05:26 PM

Hi,

It's somewhat difficult to comment without seeing the workbook and understanding exactly what you're trying to do and what results you expect.

That said it may well be that a Pivot Table will summarise your data for you and allow you to filter it to show varying sub totals.

Upload the workbook if you need further help.

**Daniel Tou** · 05-11-2017, 05:11 AM

I uploaded the workbook.What i want is that when i filter the column Nume Branch
the function below to be able to show the result based on the data filtered not the entire workbook.

=((SUMPRODUCT((K2:K37409<>"")*(O2:O37409>0)))+
(SUMPRODUCT((K2:K37409<>"")*(O2:O37409=0)*(J2:J37409<>"NU")))+
(SUMPRODUCT((K2:K37409<>"")*(I2:I37409="TAX PAYMENTS")*(AN2:AN37409>0))))/
((SUMPRODUCT((K2:K37409<>"")*(AL2:AL37409="Activ")))-
(SUMPRODUCT((K2:K37409<>"")*(AL2:AL37409="Activ")*(G2:G37409="PJ_CLOSED")))-
(SUMPRODUCT((K2:K37409<>"")*(AL2:AL37409="Activ")*(AP2:AP37409<>"")))-
(SUMPRODUCT((K2:K37409<>"")*(AL2:AL37409="Activ")*(AM2:AM37409<>""))))