+ Reply to Thread
Results 1 to 3 of 3

How to use subtotal in sumproduct with multiple criteria

  1. #1
    Registered User
    Join Date
    10-04-2015
    Location
    Romania
    MS-Off Ver
    2007,2013
    Posts
    11

    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. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,111

    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.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    10-04-2015
    Location
    Romania
    MS-Off Ver
    2007,2013
    Posts
    11

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] sumproduct subtotal offset with multiple criteria
    By reneevic in forum Excel General
    Replies: 10
    Last Post: 07-02-2019, 04:57 PM
  2. [SOLVED] Sumproduct, Subtotal with multiple condition
    By ravi.jalani in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-01-2013, 12:18 PM
  3. Need column with subtotal of multiple criteria groups
    By markmellow in forum Excel General
    Replies: 3
    Last Post: 08-01-2012, 09:18 PM
  4. Replies: 2
    Last Post: 07-03-2012, 12:45 PM
  5. Replies: 5
    Last Post: 04-20-2012, 08:54 AM
  6. Embed COUNTIF in SUBTOTAL with multiple criteria
    By 2Excel in forum Excel General
    Replies: 5
    Last Post: 05-04-2011, 12:19 PM
  7. subtotal - multiple criteria
    By mdma in forum Excel Formulas & Functions
    Replies: 102
    Last Post: 09-06-2005, 07:05 PM

Tags for this Thread

Bookmarks

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