I need help with the Sumif Function

[Guest]

I have a simple spreadsheet with numerical codes in column A. in column B i
have figures against each code. What i would like to do is use the sumif
function or any other function that would total the figures within a range of
codes. For example if the code is greater than 5000 but less than 7000 total
the figures in colum B for those codes.

The formula i have tried with no luck is

=SUMIF(A4:A20,">5000"&"<7000",B4:B20)

It seems to be the 2 conditions that are causing the problem, it works fine
with one condition but not with two.

Please advise

Thanx
Mark

 

[zackb]

Hi Mark,

SUMIF cannot handle anymore than one condition. You'll either need an array
formula, a SumProduct formula, or two SumIf formulas. Depending on who you
talk to, some are better than others. If you are not using this often, or
have a small workbook, it's six one way and half-a-dozen the other.

Try ..

=SUMIF(A4:A20,"<7000",B4:B20)-SUMIF(A4:A20,"<5000",B4:B20)

 

[Ken Wright]

=SUMIF(A4:A20,">5000",B4:B20)-SUMIF(A4:A20,">=7000",B4:B20)

Sum everything above 5K, then sum everything above or equal to 7K and take
it away from the first calc.

 

[Bob Phillips]

=SUMPRODUCT(--(A4:A20>5000),--(A4:A20<7000),B4:B20)