Add per dates

[Annette]

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!

[SteveG]

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

[DaveO]

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!
>
>
>

[Annette]

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" <DaveO@discussions.microsoft.com> wrote in message
news:31A61D3F-6B0F-41AB-9252-4E7E18469040@microsoft.com...
> 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!
>>
>>
>>