Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
=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 |
#4
|
|||
|
|||
=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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to nest a left function within a sumif function? | Excel Worksheet Functions | |||
SUMIF function | Excel Worksheet Functions | |||
SUMIF function | Excel Worksheet Functions | |||
help with "criterea" in the sumif function | Excel Worksheet Functions | |||
how do you do a sumif function on more than one worksheet? | Excel Worksheet Functions |