Hi,

I have a spreadsheet which has a column containing a date, and a column
containing a financial value. I then have columns headed with months - eg
Sep-09, Oct-09, Nov-09 etc.

I would like to display the financial value in the column for the month that
the date falls into.

I've tried various combinations of IF formulas but I can't get the
comparison with the dates to work. Can anyone help? I think the problem is
due to the fact that the date in each row is a specific date, and the column
headers are displayed as months but the value is actually the first of the
month. I'm happy to mess around with the data so one option might be a
formula to convert the specific dates to be the first day of the month, or
alternatively if there is a way to extract the month & year from a date and
compare those that might work...

I'm familiar with macros as well so if it's easier that way I'm happy to
take that on!

Many thanks,
Anna

From your description it would seem that all your data is in one
sheet. Assume your dates are in column A with the financial amount in
column B, and that your month headings start in D1 going across. Put
this formula in D2:

=SUMPRODUCT(--(DATE(YEAR($A2:$A100),MONTH($A2:$A100),1)=D$1),
$B2:$B100)

Adjust the ranges to suit your data, then copy across to give you the
totals under each month heading.

Hope this helps.

Pete

On Jan 27, 10:38*am, glitterbug99
wrote:
Hi,

I have a spreadsheet which has a column containing a date, and a column
containing a financial value. I then have columns headed with months - eg
Sep-09, Oct-09, Nov-09 etc.

I would like to display the financial value in the column for the month that
the date falls into.

I've tried various combinations of IF formulas but I can't get the
comparison with the dates to work. Can anyone help? I think the problem is
due to the fact that the date in each row is a specific date, and the column
headers are displayed as months but the value is actually the first of the
month. I'm happy to mess around with the data so one option might be a
formula to convert the specific dates to be the first day of the month, or
alternatively if there is a way to extract the month & year from a date and
compare those that might work...

I'm familiar with macros as well so if it's easier that way I'm happy to
take that on!

Many thanks,
Anna

With data arranged as below

Col A Col B Col C Col D Col E
Date Value Jan-10 Feb-10 Mar-10
1/27/2010 123 123
2/27/2010 152 152
3/27/2010 362 362

try the below formula in cell C2 and copy down and across as required.
=IF(TEXT(C$1,"mmyy")=TEXT($A2,"mmyy"),$B2,"")

--
Jacob


"glitterbug99" wrote:

Hi,

I have a spreadsheet which has a column containing a date, and a column
containing a financial value. I then have columns headed with months - eg
Sep-09, Oct-09, Nov-09 etc.

I would like to display the financial value in the column for the month that
the date falls into.

I've tried various combinations of IF formulas but I can't get the
comparison with the dates to work. Can anyone help? I think the problem is
due to the fact that the date in each row is a specific date, and the column
headers are displayed as months but the value is actually the first of the
month. I'm happy to mess around with the data so one option might be a
formula to convert the specific dates to be the first day of the month, or
alternatively if there is a way to extract the month & year from a date and
compare those that might work...

I'm familiar with macros as well so if it's easier that way I'm happy to
take that on!

Many thanks,
Anna