Excel – Use a range of cells as criteria in SUMIF

microsoft excelworksheet-function

As an example, I have the below formula:

=SUMIF(G1:G25,E1,H1:H25)+SUMIF(G1:G25,E2,H1:H25)+SUMIF(G1:G25,E3,H1:H25)

It is 3 SUMIFs all using the same criteria and sum range, the criteria uses 3 cells all in the same range.

I want to be able to condense this formula to something like the below:

=SUMIF(G1:G25,E1:E3,H1:H25)

where the criteria is the range of cells.
I have tried:

{=SUMIF(G1:G25,E1:E3,H1:H25)}

&

=SUMIF(G1:G25,{E1:E3},H1:H25)

&

=SUM(SUMIF(G1:G25,{E1:E3},H1:H25))

Is there a way to achieve this ? perhaps even with SUMPRODUCT ?

Also, in place of the range E1:E3 I would like to use a named range, if possible, If not just a way of condensing the multiple SUMIFs will do for me.

Best Answer

The smallest possible Formula I would like to suggest is:

=SUMPRODUCT(ISNUMBER(MATCH(A1:A7,A9:A11,0))*B1:B7)

enter image description here

Your Formula should be re-written like shown below:

=SUMPRODUCT(ISNUMBER(MATCH(G1:G25,E1:E3,0))*H1:H25)

You may adjust cell references in the Formula as needed.

Related Question