Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif from multiple columns
I have an list of items with multiple columns of values attached.
Col A = items number (all unique) Col B = class number (the basis for summarizing) Col C thru Z = daily values for each item Row 1 = date I want to be able to summarize by class number (which I would normally use SUMIF) and for selected colums (i.e. one or more days) In other words - what is the sum of class X on day Y what formula or formulas would I use? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif from multiple columns
On Feb 1, 11:06*pm, dcoxdcox
wrote: I have an list of items with multiple columns of values attached. Col A = items number (all unique) Col B = class number (the basis for summarizing) Col C thru Z = daily values for each item Row 1 = date I want *to be able to summarize by class number (which I would normally use SUMIF) and for selected colums (i.e. one or more days) In other words - what is the sum of class X on day Y what formula or formulas would I use? It depends what version of Excel you are using, in 2007 you just use the function sumif, but since you haven't figured that out I'm guessing you aren't. Try copying this one below the date column you want to sum: =SUMPRODUCT(($B$2:$B21="X")*(C$2:C21)) Per Erik |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif from multiple columns
sumproduct is more complete than sumif and you can perform conditional sum
with more than one condition easily. In this case use can use: sumproduct(--(C2:C10="daily value")),--(B2:B10="class number")) This will give sum of class numbers for specified daily value. You can add as much much as daily value you want. "dcoxdcox" wrote: I have an list of items with multiple columns of values attached. Col A = items number (all unique) Col B = class number (the basis for summarizing) Col C thru Z = daily values for each item Row 1 = date I want to be able to summarize by class number (which I would normally use SUMIF) and for selected colums (i.e. one or more days) In other words - what is the sum of class X on day Y what formula or formulas would I use? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif from multiple columns
I guess I'm a little out of date- using Excel 2003
"Per Erik Midtrød" wrote: On Feb 1, 11:06 pm, dcoxdcox wrote: I have an list of items with multiple columns of values attached. Col A = items number (all unique) Col B = class number (the basis for summarizing) Col C thru Z = daily values for each item Row 1 = date I want to be able to summarize by class number (which I would normally use SUMIF) and for selected colums (i.e. one or more days) In other words - what is the sum of class X on day Y what formula or formulas would I use? It depends what version of Excel you are using, in 2007 you just use the function sumif, but since you haven't figured that out I'm guessing you aren't. Try copying this one below the date column you want to sum: =SUMPRODUCT(($B$2:$B21="X")*(C$2:C21)) Per Erik |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif from multiple columns
I've seen other threads that pose this solution, but if I understand
correctly, this works if the data is organized in 4 columns A=item number, B=class number, C=date, D=value. What I have is many columns with each column being the values for 1 day. I need a formula that I can specify a class and a date or range of dates (i.e. columns) As noted in another reply, I'm using Excel 2003 "Khoshravan" wrote: sumproduct is more complete than sumif and you can perform conditional sum with more than one condition easily. In this case use can use: sumproduct(--(C2:C10="daily value")),--(B2:B10="class number")) This will give sum of class numbers for specified daily value. You can add as much much as daily value you want. "dcoxdcox" wrote: I have an list of items with multiple columns of values attached. Col A = items number (all unique) Col B = class number (the basis for summarizing) Col C thru Z = daily values for each item Row 1 = date I want to be able to summarize by class number (which I would normally use SUMIF) and for selected colums (i.e. one or more days) In other words - what is the sum of class X on day Y what formula or formulas would I use? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif from multiple columns
How are you selecting which days to sum? Is the date you want to select in
row 1 of the particular column? And how do you specify which date(s) to select? Regards, Fred. "dcoxdcox" wrote in message ... I've seen other threads that pose this solution, but if I understand correctly, this works if the data is organized in 4 columns A=item number, B=class number, C=date, D=value. What I have is many columns with each column being the values for 1 day. I need a formula that I can specify a class and a date or range of dates (i.e. columns) As noted in another reply, I'm using Excel 2003 "Khoshravan" wrote: sumproduct is more complete than sumif and you can perform conditional sum with more than one condition easily. In this case use can use: sumproduct(--(C2:C10="daily value")),--(B2:B10="class number")) This will give sum of class numbers for specified daily value. You can add as much much as daily value you want. "dcoxdcox" wrote: I have an list of items with multiple columns of values attached. Col A = items number (all unique) Col B = class number (the basis for summarizing) Col C thru Z = daily values for each item Row 1 = date I want to be able to summarize by class number (which I would normally use SUMIF) and for selected colums (i.e. one or more days) In other words - what is the sum of class X on day Y what formula or formulas would I use? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif from multiple columns
Hi,
To pick the dates set up a range with all the dates you have from C1:Z1. I set them up vertically in A19:A42. When you want to include the date enter True in column B next to the dates. In A18 enter the Class Number you want summed. Then use the following array formula where B2:B16 are the Class Numbers, C2:Z16 is all the data under the dates: =SUM(($B$2:$B$16=A18)*($C$2:$Z$16)*(TRANSPOSE(($B$ 19:$B$42=TRUE)*($A$19:$A$42))=$C$1:$Z$1)) To make it an array you must press Shift+Ctrl+Enter not Enter. Also note that you can use SUMPRODUCT but you will still need to enter it as an array. -- If this helps, please click the Yes button Cheers, Shane Devenshire "dcoxdcox" wrote: I've seen other threads that pose this solution, but if I understand correctly, this works if the data is organized in 4 columns A=item number, B=class number, C=date, D=value. What I have is many columns with each column being the values for 1 day. I need a formula that I can specify a class and a date or range of dates (i.e. columns) As noted in another reply, I'm using Excel 2003 "Khoshravan" wrote: sumproduct is more complete than sumif and you can perform conditional sum with more than one condition easily. In this case use can use: sumproduct(--(C2:C10="daily value")),--(B2:B10="class number")) This will give sum of class numbers for specified daily value. You can add as much much as daily value you want. "dcoxdcox" wrote: I have an list of items with multiple columns of values attached. Col A = items number (all unique) Col B = class number (the basis for summarizing) Col C thru Z = daily values for each item Row 1 = date I want to be able to summarize by class number (which I would normally use SUMIF) and for selected colums (i.e. one or more days) In other words - what is the sum of class X on day Y what formula or formulas would I use? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumif for multiple columns | Excel Worksheet Functions | |||
Sumif Multiple Columns | Excel Worksheet Functions | |||
SUMIF and multiple columns | Excel Discussion (Misc queries) | |||
SUMIF over multiple columns | Excel Worksheet Functions | |||
Sumif over multiple columns | Excel Worksheet Functions |