I need help with the Sumif Function
 

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

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)

--
Regards,
Zack Barresse, aka firefytr

"Mark" wrote in message
...
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

=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.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Mark" wrote in message
...
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

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

--
HTH

Bob Phillips

"Mark" wrote in message
...
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