Sum with criteria range

[blcexcel]

I have another tricky formula I need help with. I have attached an example. I'm trying to solve for a total in cell G11. It needs to sum the locations listed in range G7..G9 for the Sub# in cell G5. It is the multiple locations as a criteria that is giving me trouble. Note: normally I would concatenate Sub & Location for a lookup but there are reasons I don't want to do that. This is a simplified example. Thanks in advance!

 

[Chihiro]

Something like below,
=SUM(SUMIFS(C5:C34,A5:A34,G5,B5:B34,G7:G9))

Confirmed as array (CSE)

 

[Luke M]

Non-array version (although I like Chihiro's better)
=SUMPRODUCT((A5:A34=G5)*(COUNTIF(G7:G9,B5:B34)>0)*C5:C34)

 

[blcexcel]

Something like below,
=SUM(SUMIFS(C5:C34,A5:A34,G5,B5:B34,G7:G9))

I thought I tried this exact formula and got nothing but I tried this and it works! Thanks!

 

[blcexcel]

Non-array version (although I like Chihiro's better)
=SUMPRODUCT((A5:A34=G5)*(COUNTIF(G7:G9,B5:B34)>0)*C5:C34)

Thanks! Sometimes array formulas are a problem to use.

 

[bines53]

Hi blcexcel,

Another one, without CSE

=SUM(MMULT(FREQUENCY(G7:G9,(A5:A34=G5)*B5:B34)*C5:C35,1)),

Added one row, marked in red, this version is faster than COUNTIF in my opinion.

Davidhttp://chandoo.org/forum/members/blcexcel.5330/

 

[Chihiro]

@blcexcel

Other option is to do simple addition of multiple SUMIFS. Might be better suited if you need to hand off the sheet for someone else to manage.

=SUMIFS(C5:C34,A5:A34,G5,B5:B34,G7)+SUMIFS(C5:C34,A5:A34,G5,B5:B34,G8)+SUMIFS(C5:C34,A5:A34,G5,B5:B34,G9)

Array formula is simply combining above by nesting it in SUM(Array).

See link for detailed explanation and variations of SUMIFS with OR condition.
http://www.excel-university.com/sumifs-with-or/