# I need help with an easy formula!

#### Gekopexcel

##### New Member
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

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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.

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

Thanks a lot!

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

Gekopexcel said:
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

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!

Gekopexcel said:
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.

"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.

Replies
8
Views
284
Replies
5
Views
237
Replies
3
Views
304
Replies
2
Views
161
Replies
6
Views
559

1,203,317
Messages
6,054,711
Members
444,742
Latest member
jmartin9247

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back