Using SUMIFS with multiple OR criteria accessed by cell values.

[Chaves107]

Hi,

I am trying to calculate totals from a column based on multiple criteria. But these criteria are randomly generated each time for the purpose of what I'm doing, so I am using cell values (A10, B10, etc) rather than a number or text string.

So, there are multiple random outputs from a column range and I want to capture the corresponding number to those random outputs.

I have tried SUMIFS with multiple criteria but that is AND rather than OR and produces a result of 0.
I have tried using {} to contain multiple OR criteria but it doesn't appear to work for cell values, only text or numbers.
I have tried lots of additions SUMIF + SUMIF, etc, which works but in my actual spreadsheet the equation is 486 characters long (and there are more than one number column, so lots of these) so I want to try and avoid this if possible.

I've been trying different options using a simple spreadsheet, where outputs in cells below in A8 and B8 could be "Joe" and "David" to represent the random outputs. In this example I would be looking for 5 as an answer (2+3).

Names	Tennis Rackets
Sid	4
Terry	1
Joe	2
Billy	1
David	3

<tbody>
</tbody>

An example of a formula I tried: =SUM(SUMIFS(B2:B6,A2:A6,{A8,B8}))

Any help is gratefully appreciated.

 

[Brian from Maui]

Try

=SUMPRODUCT(--(ISNUMBER(MATCH(B1:B10,D1:D2,0))),H1:H10)

Where D1:D2 are your criterias

 

[Weazel]

I was thinking your formula was close but would require the use of sumproduct and the criteria would just be the cell references.


C1 =SUMPRODUCT(SUMIFS(B2:B6,A2:A6,D1:E1))

where D1 and E1 contain your criteria

	A	B	C	D	E
1	Names</SPAN>	Tennis Rackets</SPAN>	5</SPAN>	joe</SPAN>	david</SPAN>
2	Sid</SPAN>	4</SPAN>
3	Terry</SPAN>	1</SPAN>
4	Joe</SPAN>	2</SPAN>
5	Billy</SPAN>	1</SPAN>
6	David</SPAN>	3</SPAN>

<TBODY>
</TBODY>

 

[Chaves107]

Thanks Brian. It works! So, as long as the criteria cell values are an array it calculates it. I'll tweak my spreadsheet so that I can do this. Thanks again

 

[Chaves107]

Thanks Weazel. I tried it, and you're right. I was calculating individual cells separating them with commas, but using an array, Excel doesn't have a problem with it.