How to use subtotal in sumproduct with multiple criteria

1. How to use subtotal in sumproduct with multiple criteria

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<>""))))

2. Re: How to use subtotal in sumproduct with multiple criteria

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.

3. Re: How to use subtotal in sumproduct with multiple criteria

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<>""))))

There are currently 1 users browsing this thread. (0 members and 1 guests)