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

[TABLE="width: 500"]
<tbody>[TR]
[TD]Names
[/TD]
[TD]Tennis Rackets
[/TD]
[/TR]
[TR]
[TD]Sid
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]Terry
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Joe
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Billy
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]David
[/TD]
[TD]3
[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="class: grid, width: 368"]
<TBODY>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD="align: right"]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Names</SPAN>
[/TD]
[TD]Tennis Rackets</SPAN>
[/TD]
[TD="align: right"]5</SPAN>
[/TD]
[TD]joe</SPAN>
[/TD]
[TD]david</SPAN>
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Sid</SPAN>
[/TD]
[TD]4</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Terry</SPAN>
[/TD]
[TD]1</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Joe</SPAN>
[/TD]
[TD]2</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Billy</SPAN>
[/TD]
[TD]1</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]David</SPAN>
[/TD]
[TD]3</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

 

[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.