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))

Maybe this...

Formula:

Please Login or Register  to view this content.

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.

You're welcome. Thanks for the feedback!

=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