Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Totals Lookup by month and year
Hello:
I have a sheet like this: A B C D E F G H I 1 2009 2009 2009 2010 2010 2010 2010 2011 2 11 11 12 1 1 2 3 3 3 4 Plastic 5 9 6 10 9 2 11 8 5 Metal 1 0 0 2 8 12 1 1 6 Wood 0 7 6 5 1 2 7 8 This sheet has a list in column A of many items and every column is the total for that day. So column B has 5 plastic on November 2009. and Column C had 9 plastic on november 2009. I want to use a formula to total how many plastic were sold in november 2009. The problem is, that the above chart grows everyday, with new columns every day, which formula can I use to look up the whole row and total all amount for plastic for a particular month. I have this formula which did it for a specific range, but I need it to look in the whole row. =SUMPRODUCT(--(Sheet1!$B$1:$F$1=2008),--(Sheet1!$B$2:$F$2=sheet1!D$4),(Sheet1!$C410:$I4)) Thanks for your help. Art |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Totals Lookup by month and year
Not sure I really understand how the columns can increase but here goes:
1) why not expand the formula to include more rows and columns if they are currently empty =SUMPRODUCT(--(Sheet1!B1:Z1=2008)*(Sheet1!B2:Z2=D$4)*Sheet1!B4:Z 104) Note you must use the * method rather than the double negation when the arrays are of dissimilar size 2) if you have Excel 2007, you can use full row/column references as in (Sheet!1:1=2008) best wishes "art" wrote in message ... Hello: I have a sheet like this: A B C D E F G H I 1 2009 2009 2009 2010 2010 2010 2010 2011 2 11 11 12 1 1 2 3 3 3 4 Plastic 5 9 6 10 9 2 11 8 5 Metal 1 0 0 2 8 12 1 1 6 Wood 0 7 6 5 1 2 7 8 This sheet has a list in column A of many items and every column is the total for that day. So column B has 5 plastic on November 2009. and Column C had 9 plastic on november 2009. I want to use a formula to total how many plastic were sold in november 2009. The problem is, that the above chart grows everyday, with new columns every day, which formula can I use to look up the whole row and total all amount for plastic for a particular month. I have this formula which did it for a specific range, but I need it to look in the whole row. =SUMPRODUCT(--(Sheet1!$B$1:$F$1=2008),--(Sheet1!$B$2:$F$2=sheet1!D$4),(Sheet1!$C410:$I4)) Thanks for your help. Art |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Totals Lookup by month and year
Excel 2007 Pivot Table
Source data range grows automatically (dynamic). No formulas needed. http://www.mediafire.com/file/rmj5jm...06_07_09a.xlsx |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Totals Lookup by month and year
Hi,
Here is one solution: Suppose you create a summary area like this in the range A17:B20 (extend as needed). Enter the dates on row 17 as the first of the month but format them to show only month and year if you wish. 11/1/2009 12/1/2009 Plastic 14 6 Metal 1 0 Wood 7 6 Suppose you raw data starts in A1 and you want to leave enought room to go out to column Z. With the first year in B1 and Plastic in A3. Enter the following array formula in B18 and copy it down and to the right as far as necessary. =SUM(IF(ISNUMBER(DATE($B$1:$Z$1,$B$2:$Z$2,1))*($A$ 3:$A$5=$A18),(DATE($B$1:$Z$1,$B$2:$Z$2,1)=B$17)*$B $3:$Z$5,"")) There is an overall structural problem with your data. If you lay it out as follows you can simplify the whole process: Date Product Quantity 11/1/2009 Plastic 5 11/1/2009 Plastic 9 11/1/2009 Metal 1 ..... Summarization of such a data layout would require a far less complicated formula or could be done easily with a pivot table. As discussed earlier you can format your dates to show only Month and Year. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "art" wrote: Hello: I have a sheet like this: A B C D E F G H I 1 2009 2009 2009 2010 2010 2010 2010 2011 2 11 11 12 1 1 2 3 3 3 4 Plastic 5 9 6 10 9 2 11 8 5 Metal 1 0 0 2 8 12 1 1 6 Wood 0 7 6 5 1 2 7 8 This sheet has a list in column A of many items and every column is the total for that day. So column B has 5 plastic on November 2009. and Column C had 9 plastic on november 2009. I want to use a formula to total how many plastic were sold in november 2009. The problem is, that the above chart grows everyday, with new columns every day, which formula can I use to look up the whole row and total all amount for plastic for a particular month. I have this formula which did it for a specific range, but I need it to look in the whole row. =SUMPRODUCT(--(Sheet1!$B$1:$F$1=2008),--(Sheet1!$B$2:$F$2=sheet1!D$4),(Sheet1!$C410:$I4)) Thanks for your help. Art |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count month when date is in day/month/year format | Excel Worksheet Functions | |||
SUMIF? week, month & year-to-date totals | Excel Worksheet Functions | |||
sum year to date totals for particular month | Excel Worksheet Functions | |||
Tell me which "season" (Month/Day through Month/Day) a date(Month/Day/Year) falls in (any year)??? | Excel Discussion (Misc queries) | |||
trying to get day/month/year froamt while user enters year only | New Users to Excel |