I need help with an easy formula!

[Gekopexcel]

Hello. I need some help
I have column A with everyday dates and column B with specific number changing everyday.

At the end of the month I have to know the total of column B for each month. For example:

A B
1 7/29/2005 7
2 7/30/2005 11
3 7/31/2005 4
4 8/1/2005 5
5 8/2/2005 8
etc

So July is 22, and august 17

Is there a formula to calculate this instead of using the +sum formula each month?
Thanks

 

[tactps]

You could use:

=SUMPRODUCT(--((MONTH(A2:A6)=7))*B2:B6)
for July
=SUMPRODUCT(--((MONTH(A2:A6)=8))*B2:B6)
for August.

or use a pivot table.

 

[PaddyD]

I'd use a pivot table, grouping the dates by year and month.

 

[Gekopexcel]

Thanks a lot!

 

[Gekopexcel]

I still have one question regarding this....

My date list is very long (whole year record) so the formula tactps sent me, I can not use. (thanks anyway)
I am trying to use the pivot but how do I group the dates by month?
I get the subtotal but for each individual day and I need the whole month.
Thanks

 

[texasalynn]

I still have one question regarding this....

My date list is very long (whole year record) so the formula tactps sent me, I can not use. (thanks anyway)
I am trying to use the pivot but how do I group the dates by month?
I get the subtotal but for each individual day and I need the whole month.
Thanks

why can't you use the sumproduct? Just change the formula for the proper range

 

[Gekopexcel]

You are right...

I tried again and now it works!
I guess I made a mistake because it gave me #num as answer....but now it works! Thanks guys for your time!

 

[Brian from Maui]

I still have one question regarding this....

My date list is very long (whole year record) so the formula tactps sent me, I can not use. (thanks anyway)
I am trying to use the pivot but how do I group the dates by month?
I get the subtotal but for each individual day and I need the whole month.
Thanks

When you drag the date to the row field, right click, select Group and select month.

A slight edit to the SUMPRODUCT formula,

=SUMPRODUCT(--(MONTH(A2:A6)=1),B2:B6)

For a year test,

=SUMPRODUCT(--(A2:A11-DAY(A2:A11)+1=C1),B2:B11)

Where C1 houses the first day of the month of interest.

The #NUM is because no whole columns are accepted, use a defined and equal range for both.

 

[PaddyD]

"I am trying to use the pivot but how do I group the dates by month? "

Set up the pivot table with the 'raw dates' in, eg the row field. Once you've finished & the pivot is on the sheet, right click on the date header - go to grroup & outline, then group, then do the grouping.