Differentiating data by year into columns
 

I currently have 3 columns in my spreadsheet, that pulls information
from a data dump I get from a database. The 3 columns are based on
years (2006, 2005, 2004). I have them setup as

2004 2005 2006
=Now()-730 =Now()-365 =Now()

so that they dynamically change when the year changes. Below them the
rows contain the information I want displayed by year. However, the
date column in the data dump I get gives dates formatted like this:
2005-09
I have a dsum that looks at a different column, and sums based on
country, but in order to make sure that the values appear based on the
year in the date column, how would I set that up? I obviously need to
be able to look at only the first 4 characters in the date fields
values, but how? Plus, it needs to look at the year in the column
header, and only give the values related to that year from the date
column in the data dump.

Any and all help is greatly appreciated!


--
Toon
------------------------------------------------------------------------
Toon's Profile: http://www.excelforum.com/member.php...o&userid=31337
View this thread: http://www.excelforum.com/showthread...hreadid=515421

Differentiating data by year into columns
 

Would SUMPRODUCT work for you?

Try,

=SUMPRODUCT((YEAR(Sheet2!A2:A6)=YEAR(A1))*(Sheet2! B2:B6="USA")*(Sheet2!C2:C6))

Sheet2! is your data dump. A:A is Date, B:B is Country and C:C are the
values to sum. A1 is where your NOW()-730 is located. You can refer to
a cell that contains the country name rather than typing in the
country.

Just an FYI, if your data dump has headers like "Date" in the first
row, your SUMPRODUCT range should start on the second row. That is
because the YEAR function of text like Date will return a VALUE error.

Does that help?

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=515421