Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif and range validation
Hi. I need to sum some numbers and all these are linked to accounts. Lets say I have accounts in column a: 4001 4002 ... 4998 4999 and I want to sum values from column b in groups ranging from eg. 4230-4239, where the boundries of these ranges are defined elsewhere. The accounts are ussually ground in 10s, i.e. from 4230 to 4239 is related to the same "thing". =sumif(col a, Rangeproblem, col b) How do I solve the range problem? /Møller -- c991257 ------------------------------------------------------------------------ c991257's Profile: http://www.excelforum.com/member.php...o&userid=36549 View this thread: http://www.excelforum.com/showthread...hreadid=573914 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif and range validation
A guess
=SUMIF(A:A,"=4230",B:B)-SUMIF(A:A,"4239",B:B) -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (Remove ^^ from email) "c991257" wrote in message ... Hi. I need to sum some numbers and all these are linked to accounts. Lets say I have accounts in column a: 4001 4002 .. 4998 4999 and I want to sum values from column b in groups ranging from eg. 4230-4239, where the boundries of these ranges are defined elsewhere. The accounts are ussually ground in 10s, i.e. from 4230 to 4239 is related to the same "thing". =sumif(col a, Rangeproblem, col b) How do I solve the range problem? /Møller -- c991257 ------------------------------------------------------------------------ c991257's Profile: http://www.excelforum.com/member.php...o&userid=36549 View this thread: http://www.excelforum.com/showthread...hreadid=573914 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif and range validation
That would be a sloution, but I would prefer to take the range ends from two separate cells. Something like =sumif(A:A, and(A:A=C1,A:A<=C2), B:B) any ideas? -- c991257 ------------------------------------------------------------------------ c991257's Profile: http://www.excelforum.com/member.php...o&userid=36549 View this thread: http://www.excelforum.com/showthread...hreadid=573914 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif and range validation
You can't do that with SUMIF unless you use 2007 and then there is a new
function called SUMIFS, nevertheless you can use =SUMPRODUCT(--(A1:A10000=C1),--(A1:A10000<=C2),B1:B10000) note that you cannot use the whole column so you need to specify the range -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (Remove ^^ from email) "c991257" wrote in message ... That would be a sloution, but I would prefer to take the range ends from two separate cells. Something like =sumif(A:A, and(A:A=C1,A:A<=C2), B:B) any ideas? -- c991257 ------------------------------------------------------------------------ c991257's Profile: http://www.excelforum.com/member.php...o&userid=36549 View this thread: http://www.excelforum.com/showthread...hreadid=573914 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumif range to end of data | Excel Discussion (Misc queries) | |||
SUMIF not adding a range | Excel Discussion (Misc queries) | |||
SUMIF using a multiple-column range | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions | |||
SUMIF across a range of worksheets | Excel Worksheet Functions |