+ Reply to Thread
Results 1 to 8 of 8

Shorten SUMIF + SUMIF + SUMIF each with same reference range but different criteria???

  1. #1
    Forum Contributor
    Join Date
    04-14-2013
    Location
    Ky
    MS-Off Ver
    Excel 2013
    Posts
    344

    Shorten SUMIF + SUMIF + SUMIF each with same reference range but different criteria???

    My formula works fine... but is rather lengthy. Is there a short way to express it? See below, it's basically adding categories (criteria) of any "Blue" and any "Orange" and any "Yellow" text in a given range on another worksheet (that's the INDIRECT) and adding the 3 together. I need the total even if here are not any or one or two of the categories.

    Formula: copy to clipboard
    Please Login or Register  to view this content.



    BONUS QUESTION:

    You see those 3 extra spaces in my criteria, i.e. "<space><space><space>BLUE"? That's how the spreadsheet gets imported, and while I know the formula to remove those spaces, it'll be too complex to implement on my worksheets, I think. I have over 100 worksheets in the workbook, each with 40-50 of the criteria with the spaces. Luckily - they are are uniform, 3 spaces exactly, but it's a pain making sure my criteria formula is correct. If I miscount a space in my formula, it would mess everything up. Is there a way to write the formula above to ignore spaces?

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,391

    Re: Shorten SUMIF + SUMIF + SUMIF each with same reference range but different criteria???

    See if you can adapt the following to suite (I did not include the indirect)

    A
    B
    C
    1
    o
    1
    62
    2
    b
    2
    3
    y
    3
    4
    o
    4
    5
    b
    5
    6
    y
    6
    7
    g
    7
    8
    r
    8
    9
    t
    9
    10
    y
    10
    11
    o
    1
    12
    b
    2
    13
    y
    3
    14
    o
    4
    15
    b
    5
    16
    y
    6
    17
    g
    7
    18
    r
    8
    19
    t
    9
    20
    y
    10


    C1=SUMPRODUCT((A1:A20={"o","b","y"})*(B1:B20))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Shorten SUMIF + SUMIF + SUMIF each with same reference range but different criteria???

    Maybe this...

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Shorten SUMIF + SUMIF + SUMIF each with same reference range but different criteria???

    Ooops!

    I forgot about the Bonus Question!

    Like this...

    =SUMPRODUCT(--ISNUMBER(MATCH(TRIM(INDIRECT(A3&"B21:B90")),{"BLUE","ORANGE","YELLOW"},0)),INDIRECT(A3&"E21:E90"))

    Assumes those spaces really are char 32 space characters and not char 160 nbsp characters.

  5. #5
    Forum Contributor
    Join Date
    04-14-2013
    Location
    Ky
    MS-Off Ver
    Excel 2013
    Posts
    344

    Re: Shorten SUMIF + SUMIF + SUMIF each with same reference range but different criteria???

    Both worked great, Tony. Thanks!!

  6. #6
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Shorten SUMIF + SUMIF + SUMIF each with same reference range but different criteria???

    You're welcome. Thanks for the feedback!

  7. #7
    Forum Contributor
    Join Date
    04-14-2013
    Location
    Ky
    MS-Off Ver
    Excel 2013
    Posts
    344

    Re: Shorten SUMIF + SUMIF + SUMIF each with same reference range but different criteria???

    OK - 3 questions...

    Here's the 2 formulas:

    =SUMPRODUCT(SUMIF(INDIRECT(A3&"B21:B90"),{" BLUE"," ORANGE"," YELLOW"},INDIRECT(A3&"E21:E90")))

    and

    =SUMPRODUCT(--ISNUMBER(MATCH(TRIM(INDIRECT(A3&"B21:B90")),{"BLUE","ORANGE","YELLOW"},0)),INDIRECT(A3&"E21:E90"))


    I could go on and never analyze the work or understand it and just use the formulas - but then I never learn! So in trying to understand the syntax and structure, the first one I understand pretty well; it's fairly straightforward. However, I get confused on the second one. Tony or anyone else mind helping me learn this?

    Specifically, why the 2 dashes before ISNUMBER? Why the MATCH - what happens when ISNUMBER and TRIM are used w/o MATCH? And lastly, why that order in the nesting - ISNUMBER, then MATCH, then TRIM?

    -HeyInKy

  8. #8
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Shorten SUMIF + SUMIF + SUMIF each with same reference range but different criteria???

    =SUMPRODUCT(--ISNUMBER(MATCH(TRIM(INDIRECT(A3&"B21:B90")),{"BLUE","ORANGE","YELLOW"},0)),INDIRECT(A3&"E21:E90"))

    You asked for a method to calculate that ignores the space characters because you may not have a consistent number of spaces. So, we have to use the TRIM function to remove those spaces.

    The TRIM function removes any leading/trailing and multiple interspersed char 32 space characters. For example, your data apparently looks something like this (the underscores represent space characters):

    ___Blue
    ___Orange
    ___Yellow

    TRIM("___Blue") = Blue
    TRIM("___Orange") = Orange
    TRIM("___Yellow") = Yellow

    Then we compare the trimmed range SheetName!B21:B90 against the array {"BLUE","ORANGE","YELLOW"} for any matches.

    If any matches are present the MATCH function returns the array element number of the match. For example:

    Data Range
    B
    C
    D
    21
    Green
    =MATCH(B21,{"BLUE","ORANGE","YELLOW"},0)
    #N/A
    22
    Blue
    =MATCH(B22,{"BLUE","ORANGE","YELLOW"},0)
    1
    23
    Black
    =MATCH(B23,{"BLUE","ORANGE","YELLOW"},0)
    #N/A
    24
    Yellow
    =MATCH(B24,{"BLUE","ORANGE","YELLOW"},0)
    3
    25
    Red
    =MATCH(B25,{"BLUE","ORANGE","YELLOW"},0)
    #N/A


    B21 (Green) does not match {"BLUE","ORANGE","YELLOW"} so the result is the #N/A error

    B22 (Blue) does match {"BLUE","ORANGE","YELLOW"}. Blue is the 1st element of the array so the result is 1

    B23 (Black) does not match {"BLUE","ORANGE","YELLOW"} so the result is the #N/A error

    B24 (Yellow) does match {"BLUE","ORANGE","YELLOW"}. Yellow is the 3rd element of the array so the result is 3

    B25 (Red) does not match {"BLUE","ORANGE","YELLOW"} so the result is the #N/A error

    This array of numbers/errors is then passed to the ISNUMBER which does exactly what its name implies, tests the array for numbers.

    ISNUMBER(#N/A) = FALSE
    ISNUMBER(1) = TRUE
    ISNUMBER(#N/A) = FALSE
    ISNUMBER(3) = TRUE
    ISNUMBER(#N/A) = FALSE

    The SUMPRODUCT function works with numbers so we have to convert those Boolean values, TRUE and FALSE, to numbers. One way to do that is by using the double unary minus --.

    --TRUE = 1
    --FALSE = 0

    This array of 1s and 0s is then multiplied with the values in the sum range SheetName!E21:E90.

    The results of this multiplication are then summed for the final result of the formula.

    See this for more info on the SUMPRODUCT function:

    http://xldynamic.com/source/xld.SUMPRODUCT.html
    Last edited by Tony Valko; 04-13-2014 at 09:44 PM.

+ 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. SUMIF or SUMIF's formula with multiple criteria
    By jackiemariee in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-30-2013, 06:19 PM
  2. try sumif criteria reference
    By Roger Govier in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 09-06-2005, 02:05 PM
  3. [SOLVED] nested sumif or sumif with two criteria
    By dshigley in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-04-2005, 11: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