Looking for a more efficient formula using a criteria range in an SUMIF Formula

[ddillistin]

In a SUMIF or SUMIFS formula, I'm looking for he most efficient way to enter a value range in place of the 8000 to 8002, I actually need go up to 8099. My worksheet is dynamic so the cell locations containing the 8000 series #'s will vary each week.

=SUMIF(E4:E100,8000,Q4:Q100)+SUMIF(E4:E100,8001,Q4:Q100)+SUMIF(E4:E100,8002,Q4:Q100)

Thank You,

 

[Tetra201]

Is this what you need?

=SUMIFS(Q4:Q100,E4:E100,">=8000",E4:E100,"<=8099")

 

[ddillistin]

In a SUMIF or SUMIFS formula, I'm looking for he most efficient way to enter a value range in place of the 8000 to 8002, I actually need go up to 8099. My worksheet is dynamic so the cell locations containing the 8000 series #'s will vary each week.

=SUMIF(E4:E100,8000,Q4:Q100)+SUMIF(E4:E100,8001,Q4:Q100)+SUMIF(E4:E100,8002,Q4:Q100)

Thank You,

Tetra102, Thank You for your reply.
The result of the formula in your reply was #NAME ?

 

[RicoS]

That formula works for me. Are your 8000 series values formated as text as opposed to number? If so you could use:

=SUMIF($E$4:$E$100,"80*",$Q$4:$Q$100)

 

[Eric W]

You might have an older version of Excel. SUMIFS was introduced in 2007. If your numbers are saved as text, then RicoS's formula should work. If they are saved as numbers, try:

=SUMPRODUCT(Q4:Q100,--(E4:E100>=8000),--(E4:E100<=8099))

 

[Tetra201]

... The result of the formula in your reply was #NAME...

Looks like your version of Excel doesn't have the SUMIFS function. In this case, try the following workaround:

=SUMPRODUCT(Q4:Q100,--(E4:E100>=8000),--(E4:E100<=8099))