Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct by year
Having trouble getting my sumproduct to work. In col A2:A651 I have a
date, mm/dd/yy. Col D2:D651 I have catagory, gas, col E2:E651 I have amount. I'm trying to tabulate, by year, the amount of each catagory but can't get sumproduct to work. My table consists of the years 2006-2012 in g661 thru g668. How do I code the sumproduct to look just at the year in col A against G? I tried left(a2:a651,4)=G661 but am getting the #value error. I'm assuming it's the way I'm comparing YEAR? thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct by year
"Jack Deuce" wrote:
Having trouble getting my sumproduct to work. In col A2:A651 I have a date, mm/dd/yy. Col D2:D651 I have catagory, gas, col E2:E651 I have amount. I'm trying to tabulate, by year, the amount of each catagory but can't get sumproduct to work. My table consists of the years 2006-2012 in g661 thru g668. How do I code the sumproduct to look just at the year in col A against G? I tried left(a2:a651,4)=G661 but am getting the #value error. I'm assuming it's the way I'm comparing YEAR? In a nutshell: =SUMPRODUCT(--(YEAR($A$2:$A$651)=G661),$E$2:$E$651) Copy that formula down a column through row 668. If you also have categories across columns H660:Z660 for example, you might use the following formula to fill out a 2-dimensional table in H661:Z668, starting with H661: =SUMPRODUCT((YEAR($A$2:$A$651)=$G661)*($D$2:$D$651 =H$660),$E$2:$E$651) Copy that formula into H661:Z668. You cannot use LEFT() because what you see in the cell is the result of formatted (e.g. mm/dd/yyyy). The actual value is an integer like 41279 for 1/1/2013. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct by year
On Sat, 5 Jan 2013 15:32:20 -0800, "joeu2004"
wrote: "Jack Deuce" wrote: Having trouble getting my sumproduct to work. In col A2:A651 I have a date, mm/dd/yy. Col D2:D651 I have catagory, gas, col E2:E651 I have amount. I'm trying to tabulate, by year, the amount of each catagory but can't get sumproduct to work. My table consists of the years 2006-2012 in g661 thru g668. How do I code the sumproduct to look just at the year in col A against G? I tried left(a2:a651,4)=G661 but am getting the #value error. I'm assuming it's the way I'm comparing YEAR? In a nutshell: =SUMPRODUCT(--(YEAR($A$2:$A$651)=G661),$E$2:$E$651) Copy that formula down a column through row 668. If you also have categories across columns H660:Z660 for example, you might use the following formula to fill out a 2-dimensional table in H661:Z668, starting with H661: =SUMPRODUCT((YEAR($A$2:$A$651)=$G661)*($D$2:$D$65 1=H$660),$E$2:$E$651) Copy that formula into H661:Z668. You cannot use LEFT() because what you see in the cell is the result of formatted (e.g. mm/dd/yyyy). The actual value is an integer like 41279 for 1/1/2013. Thanks for the help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT WEEKNUM YEAR | Excel Discussion (Misc queries) | |||
sumproduct by year?? | Excel Discussion (Misc queries) | |||
Using COUNTIF or SUMPRODUCT for Year | Excel Discussion (Misc queries) | |||
sumproduct for month and year | Excel Discussion (Misc queries) | |||
sumproduct in a given year | Excel Worksheet Functions |