# 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<>""))))  Register To Reply

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.  Register To Reply

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<>""))))  Register To Reply

#### Thread Information

##### Users Browsing this Thread

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

#### Tags for this Thread #### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1