Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 30
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 829
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 30
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
SUMPRODUCT WEEKNUM YEAR sam Excel Discussion (Misc queries) 1 February 24th 10 03:41 AM
sumproduct by year?? MDI Anne Excel Discussion (Misc queries) 5 June 20th 08 07:53 PM
Using COUNTIF or SUMPRODUCT for Year PSmith Excel Discussion (Misc queries) 3 November 30th 07 04:59 PM
sumproduct for month and year Benjamin Excel Discussion (Misc queries) 1 September 20th 06 04:29 PM
sumproduct in a given year Bumblebee Excel Worksheet Functions 8 August 19th 06 06:59 PM


All times are GMT +1. The time now is 03:27 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"