Totals Lookup by month and year

[art]

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

 

[Bernard Liengme]

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:Z104)
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

 

[Herbert Seidenberg]

Excel 2007 Pivot Table
Source data range grows automatically (dynamic).
No formulas needed.
http://www.mediafire.com/file/rmj5jmmjmmz/06_07_09a.xlsx

 

[Shane Devenshire]

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.