Sumifs with multiple referenced criteria on a single range

[TheDarkRoast]

Hi there

I am trying so sum all values in column B where the account in column A is either 10000 or 20000. I manage to do this easily with the following formula:

=SUM(SUMIFS($B$2:$B$12,$A$2:$A$12,{10000;20000}))

to get to the correct answer of 219,910,716.

I am, however trying to reference the criteria directly in cells B16 and B17.

I have unsuccessfully tried =SUM(SUMIFS($B$2:$B$12,$A$2:$A$12,{B16;B17})) and I have also tried putting all the criteria in one cell like this :{10000;20000} and tried referencing it directly but with no luck.

Any help would be greatly appreciated.


Data in table below.


Regards,


Z

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Account[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]10000[/TD]
[TD] 147,901,220
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]10000[/TD]
[TD][TABLE="width: 107"]
<tbody>[TR="class: grid"]
[TD="width: 107"]71,428,844 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]20000[/TD]
[TD][TABLE="width: 107"]
<tbody>[TR="class: grid"]
[TD="width: 107"]2,400 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]20000[/TD]
[TD][TABLE="width: 107"]
<tbody>[TR="class: grid"]
[TD="width: 107"]7,092 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]20000[/TD]
[TD][TABLE="width: 107"]
<tbody>[TR="class: grid"]
[TD="width: 107"]3,180 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]20000[/TD]
[TD][TABLE="width: 107"]
<tbody>[TR="class: grid"]
[TD="width: 107"]1,638 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]20000[/TD]
[TD][TABLE="width: 107"]
<tbody>[TR="class: grid"]
[TD="width: 107"]566,342 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]30000[/TD]
[TD][TABLE="width: 107"]
<tbody>[TR="class: grid"]
[TD="width: 107"]239,992 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]30000[/TD]
[TD][TABLE="width: 107"]
<tbody>[TR="class: grid"]
[TD="width: 107"]709,190 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]30000[/TD]
[TD][TABLE="width: 107"]
<tbody>[TR="class: grid"]
[TD="width: 107"]317,996 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]30000[/TD]
[TD][TABLE="width: 107"]
<tbody>[TR="class: grid"]
[TD="width: 107"]163,800 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD]Criteria[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD]10000[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD]20000[/TD]
[/TR]
</tbody>[/TABLE]

 

[Domenic]

Try...

=SUMPRODUCT(SUMIFS($B$2:$B$12,$A$2:$A$12,B16:B17))

Hope this helps!

 

[dreid1011]

Perhaps this:

=SUMPRODUCT(((A2:A12=B16)+(A2:A12=B17))*B2:B12)

 

[TheDarkRoast]

Try...

=SUMPRODUCT(SUMIFS($B$2:$B$12,$A$2:$A$12,B16:B17))

Hope this helps!

This is perfect! Thank you!!

 

[TheDarkRoast]

Perhaps this:

=SUMPRODUCT(((A2:A12=B16)+(A2:A12=B17))*B2:B12)

I probably should have mentioned that I summarized the example tremendously and there are actually about 10 criteria. Domenic's formula works better in that instance but your one works as well but will just be cumbersome in my real world example.

Thank you though