+ Reply to Thread
Results 1 to 7 of 7

SUMPRODUCT: Multiple criteria in same column and criteria NOT hard coded

  1. #1
    Registered User
    Join Date
    08-04-2013
    Location
    Karachi, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    4

    SUMPRODUCT: Multiple criteria in same column and criteria NOT hard coded

    My data would be arranged something like this:

    A B C D

    ab 1 2 3
    cd 4 5 6
    ef 7 8 9
    gh 2 5 8

    (Text in column A and numbers in columns B,C and D)


    I would like to sum for "ab" an "cd" across all columns B,C and D. "ab" and "cd" would be linked to cells that I would like to keep variable i.e. I DO NOT want to hard code them in the formula. So if i enter "ab" in "cd" in the linked cells, I should get (1+2+3)+(4+5+6)=21 as the answer. As another example, if i change the linked cells to "gh" and "cd", I should get (2+5+8)+(4+5+6)= 30 as the answer. Please help.

    Note: I have tried using sumifs with multiple criteria in the same column, but apparently it cannot handle multiple columns (i cannot add a "total" column in the end because the sumrange would be dynamic). Sumproduct can handle multiple columns but then I would have to hard code the criteria, which I cant (as the criteria would be dynamic as well and there would be more than 50 criteria in some cases).

  2. #2
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    365
    Posts
    495

    Re: SUMPRODUCT: Multiple criteria in same column and criteria NOT hard coded

    Please post an excel file with these data and some examples of the desired results, to make clear what you want exactly.
    When I say semicolon, u say comma!

  3. #3
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: SUMPRODUCT: Multiple criteria in same column and criteria NOT hard coded

    Assuming criteria cells are F2 and G2 try this version

    =SUMPRODUCT(((A2:A10=F2)+(A2:A10=G2))*B2:D10)
    Audere est facere

  4. #4
    Registered User
    Join Date
    08-04-2013
    Location
    Karachi, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: SUMPRODUCT: Multiple criteria in same column and criteria NOT hard coded

    I have attached a file for reference.

    @dannylonglegs
    I would like to specify F2 and G2 as an array as in:
    =SUMPRODUCT((A2:A10=F2:G2)*B2:D10)

    because there would be more than 50 criteria in some cases. But specifying the criteria cells as an array does not work as it searches for both cells as a single item (in the exact order) within column A, rather than searching for them as two separate items. In a nutshell, I would like to tell excel that I want you to look for F2:G2 in A2:A10, where the value can be EITHER F2 or G2 and return TRUE or FALSE. So if I enter "cd" and "gh" in F2 and G2, it should return FALSE, TRUE, FALSE, TRUE in A2:A5. Hope I was clear enough.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-04-2013
    Location
    Karachi, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: SUMPRODUCT: Multiple criteria in same column and criteria NOT hard coded

    SOLVED.
    Hey I did something rather unusual and got it working. I used the MATCH function and switched the lookup value with the lookup array. Assuming F2:F4 contain the lookup values of "ab", "cd" and "gh" respectively, I enter:

    =IF(ISNUMBER(MATCH(A2:A5,F2:F4,0))=TRUE,1,0)

    I get an array: {1,1,0,1} in A2:A5 which is exactly what I wanted! Now I can simply sumproduct this array with B2:D5 to get the desired results.

    Regardless, thanks a lot for all your help.

  6. #6
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: SUMPRODUCT: Multiple criteria in same column and criteria NOT hard coded

    You can incorporate ISNUMBER(MATCH into a SUMPRODUCT formula to do that all in one go if you want, i.e.

    =SUMPRODUCT(ISNUMBER(MATCH(A2:A5,F2:F4,0))*B2:D5)

  7. #7
    Registered User
    Join Date
    08-04-2013
    Location
    Karachi, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: SUMPRODUCT: Multiple criteria in same column and criteria NOT hard coded

    Yeap, thats what I figured. Thankyou!

+ 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] Convert two dimensional row to column data to a hard-coded array
    By evancharles in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-07-2013, 03:15 PM
  2. Replies: 5
    Last Post: 04-20-2012, 08:54 AM
  3. Replies: 2
    Last Post: 10-05-2011, 12:43 PM
  4. Replies: 1
    Last Post: 01-21-2011, 07:24 AM
  5. Sumproduct - multiple criteria in Column A
    By briank in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-06-2005, 03:06 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