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

1. ## 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:  `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?  Register To Reply

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

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

Maybe this...

Formula:  `Please Login or Register  to view this content.`  Register To Reply

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

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

Both worked great, Tony. Thanks!!  Register To Reply

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

You're welcome. Thanks for the feedback!   Register To Reply

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

8. ## 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.

http://xldynamic.com/source/xld.SUMPRODUCT.html  Register To Reply