sumproduct by year

[Jack Deuce]

Having trouble getting my sumproduct to work. In col A2:A651 I have a
date, mm/dd/yy. Col D2651 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

 

[joeu2004]

Having trouble getting my sumproduct to work. In col A2:A651 I have a
date, mm/dd/yy. Col D2651 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.

 

[Jack Deuce]

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.

Thanks for the help.