Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Sum, based on criteria
Hi all,
Many thanks if you can help me with this. I have a table of 5 columns and 330 rows of data, in column A, each cell has one of the numbers from 1 to 8. So for example 4 could be repeated around 40 times. What I would like to do is add up all the numbers in columns B, C, D & E dependant on which one of the 8 numbers is in column A. So I would get a total for all the 1s, a total for all the 2s etc at the bottom of each of the columns Hope this makes sense and thank you Andy |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Sum, based on criteria
"Andy the yeti" wrote in message
... Hi all, Many thanks if you can help me with this. I have a table of 5 columns and 330 rows of data, in column A, each cell has one of the numbers from 1 to 8. So for example 4 could be repeated around 40 times. What I would like to do is add up all the numbers in columns B, C, D & E dependant on which one of the 8 numbers is in column A. So I would get a total for all the 1's, a total for all the 2's etc at the bottom of each of the columns Hope this makes sense and thank you Andy Use SUMPRODUCT to test multiple criteria (SUMIF can use only one criterion). See this: http://www.xldynamic.com/source/xld.SUMPRODUCT.html Ian |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Sum, based on criteria
Try this:
A335: 1 A336: 2 A337: 3 etc B335: =SUMPRODUCT(--($A$2:$A$330=A335)*($B$2:$E$330)) Copy that formula down as far as needed. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Andy the yeti" wrote: Hi all, Many thanks if you can help me with this. I have a table of 5 columns and 330 rows of data, in column A, each cell has one of the numbers from 1 to 8. So for example 4 could be repeated around 40 times. What I would like to do is add up all the numbers in columns B, C, D & E dependant on which one of the 8 numbers is in column A. So I would get a total for all the 1s, a total for all the 2s etc at the bottom of each of the columns Hope this makes sense and thank you Andy |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Sum, based on criteria
Thank you very much, those options are pefect.
Have a great Xmas all !! A. "ikr" wrote: "Andy the yeti" wrote in message ... Hi all, Many thanks if you can help me with this. I have a table of 5 columns and 330 rows of data, in column A, each cell has one of the numbers from 1 to 8. So for example 4 could be repeated around 40 times. What I would like to do is add up all the numbers in columns B, C, D & E dependant on which one of the 8 numbers is in column A. So I would get a total for all the 1's, a total for all the 2's etc at the bottom of each of the columns Hope this makes sense and thank you Andy Use SUMPRODUCT to test multiple criteria (SUMIF can use only one criterion). See this: http://www.xldynamic.com/source/xld.SUMPRODUCT.html Ian |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Sum, based on criteria
SUMIF and OR would also work for multiple conditions. Try at the bottom of B assuming that is the column to sum if A has a 1 or a 2 in it. {=SUM(IF(OR($A$22:$A$27=1,A22:A27=2),$B$22:$B$27,0 ))} Change the conditions for each column to sum as needed. Commit with Ctrl-Shift-Enter as this is an array formula. Cheers, Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=495500 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Criteria in SumProduct, N/A Result | Excel Worksheet Functions | |||
Counting by multiple criteria | Excel Worksheet Functions | |||
COUNTIF or SUM function (Multiple criteria) HELP!! | Excel Worksheet Functions | |||
Using Sumproduct with multiple Criteria | Excel Worksheet Functions | |||
Count rows based on multiple criteria | Excel Worksheet Functions |