Add per dates
 

I can't figure out how to read a date in a formula. In my column I have the
format set as mm/dd/yy, but the formula is looking at MMM and it is not
converting.

I want to add sums of money based on month, not mm/dd/yy and I think that is
where I'm faultering.

Here are details:

cell d1 = May
col c = dates (ie, 05/03/06)
col u = amounts

I want to add all the amounts that fell during the month of
May. How do I write this? THanks!

Add per dates
 

Annette,

Try using SUMPRODUCT

=SUMPRODUCT((MONTH(C1:C5)=5)*(U1:U5))

Where 5 is the month number. Change accordingly for different months
(Jan = 1, Feb = 2....Dec = 12)


HTH

Steve


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

Add per dates
 

Try this...

=SUMPRODUCT(--(MONTH(C1:C{x})=D1), (U1:U{x}))
..
All you need to do is alter cell D1 to be the number value of thwe month in
question May = 5, December = 12 etc...) and replace the {x} with a number
equal to the number of rows you have.

HTH.

"Annette" wrote:

I can't figure out how to read a date in a formula. In my column I have the
format set as mm/dd/yy, but the formula is looking at MMM and it is not
converting.

I want to add sums of money based on month, not mm/dd/yy and I think that is
where I'm faultering.

Here are details:

cell d1 = May
col c = dates (ie, 05/03/06)
col u = amounts

I want to add all the amounts that fell during the month of
May. How do I write this? THanks!

Add per dates
 

These work provided there is information in the cells. Is there a way to
provide this so that I'm including blank cells and they will add those as
data is entered? (Say the formula should cover up to row 100.)


"DaveO" wrote in message
...
Try this...

=SUMPRODUCT(--(MONTH(C1:C{x})=D1), (U1:U{x}))
.
All you need to do is alter cell D1 to be the number value of thwe month
in
question May = 5, December = 12 etc...) and replace the {x} with a number
equal to the number of rows you have.

HTH.

"Annette" wrote:

I can't figure out how to read a date in a formula. In my column I have
the
format set as mm/dd/yy, but the formula is looking at MMM and it is not
converting.

I want to add sums of money based on month, not mm/dd/yy and I think that
is
where I'm faultering.

Here are details:

cell d1 = May
col c = dates (ie, 05/03/06)
col u = amounts

I want to add all the amounts that fell during the month of
May. How do I write this? THanks!