+ Reply to Thread
Results 1 to 7 of 7

Sumifs with multiple criteria in multiple columns

  1. #1
    Registered User
    Join Date
    06-16-2010
    Location
    India, Pune-411006
    MS-Off Ver
    Excel 2007/2010
    Posts
    89

    Sumifs with multiple criteria in multiple columns

    Hi,

    I am trying to get sum based on multiple criteria from 3 columns and single criteria from 1 columns.

    I have used combination of Sumproduct and sumifs functions, however it is not working.

    Please check the attached file for details.

    Any help highly appreciated.
    Attached Files Attached Files
    /Rgds,
    Ganesh Pitale

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Sumifs with multiple criteria in multiple columns

    Try

    =SUMPRODUCT((ISNUMBER(MATCH(DATA!$A$2:$A$110,$C$8:$C$10,0)))*(ISNUMBER(MATCH(DATA!$B$2:$B$110,$D$8:$D$10,0)))*(ISNUMBER(MATCH(DATA!$C$2:$C$110,$E$8:$E$10,0)))*(DATA!$E$2:$E$110=C13)*DATA!$F$2:$F$110)

    Copy down
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Registered User
    Join Date
    06-16-2010
    Location
    India, Pune-411006
    MS-Off Ver
    Excel 2007/2010
    Posts
    89

    Re: Sumifs with multiple criteria in multiple columns

    Hi Ace_XL,

    Thanks a ton for your reply.

    Formula is working fine in case of selection of multiple criteria, however it is not working if criteria mentioned as "*" (means for all records in database).

    e.g. Earlier in sumifs function i used "*" as criteria for sum of all the records in database, have created dynamic drop-downs for selection of criteria/options for end user to view the summary of data.

    Please refer attached file for more details.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    06-16-2010
    Location
    India, Pune-411006
    MS-Off Ver
    Excel 2007/2010
    Posts
    89

    Re: Sumifs with multiple criteria in multiple columns

    Is there any alternative instead of using "*" for all the records??

  5. #5
    Registered User
    Join Date
    06-16-2010
    Location
    India, Pune-411006
    MS-Off Ver
    Excel 2007/2010
    Posts
    89

    Re: Sumifs with multiple criteria in multiple columns

    Hello Experts,

    Can any one help me out for above problem.....

  6. #6
    Registered User
    Join Date
    06-16-2010
    Location
    India, Pune-411006
    MS-Off Ver
    Excel 2007/2010
    Posts
    89

    Re: Sumifs with multiple criteria in multiple columns

    Any help on above . . . .

  7. #7
    Registered User
    Join Date
    06-16-2010
    Location
    India, Pune-411006
    MS-Off Ver
    Excel 2007/2010
    Posts
    89

    Re: Sumifs with multiple criteria in multiple columns

    Is there any solution for the same ? ? ?

+ 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] SUMIF or SUMIFS adding time where criteria is in multiple columns
    By 66ev in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-14-2013, 02:21 PM
  2. Sumifs with multiple criteria for multiple names
    By mcayea in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 05-07-2013, 10:55 AM
  3. Using a SUMIFS across multiple sheets with multiple criteria
    By Auto667 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-06-2013, 06:55 AM
  4. Replies: 2
    Last Post: 01-23-2013, 06:25 AM
  5. Replies: 1
    Last Post: 05-16-2011, 05:00 PM

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