Month Totals...

GUIDO22

Registered User.
Local time
Today, 22:23
Joined
Nov 2, 2003
Messages
515
I have a list of thousands of rows containing sets of daily records each with a datestamp...

1/1/2012
1/1/2012
1/1/2012
1/1/2012
2/1/2012
2/1/2012
3/1/2012
3/1/2012
3/1/2012
......n

I wish to deteremine the totals for each MONTH... so how would I create cells that contain totals for each month of records..... how do I do this...?

THanks in advance...
 
try using the format function ...

Any chance of a sample showing the use of FORMAT over a range of cells please...?
 
Well you cant use functions for ranges... you first make a month per line.
Then use a sumif or cross tab to make totals.
 
Image of the sumproduct formula where the month is based on a number.
Just threw together March/ April random numbers.
On the top two, the totals 20 and 330 represent the totals in dates March and April.

The A3:A97 are hard coded. A Named range could be used.
This example works for 2 months, it could be expanded to 12 months, but the years would add up all months that match.

Is this along the line of your question?
 

Attachments

  • Monthly Sum.jpg
    Monthly Sum.jpg
    47.3 KB · Views: 158
Image of the sumproduct formula where the month is based on a number.
Just threw together March/ April random numbers.
On the top two, the totals 20 and 330 represent the totals in dates March and April.

The A3:A97 are hard coded. A Named range could be used.
This example works for 2 months, it could be expanded to 12 months, but the years would add up all months that match.

Is this along the line of your question?

That is EXACTLY what I needed. Thank you very much:)!
 
=COUNTIFS(MONTH($B$6:$B$2057),"=2",$J$6:$J$2057,"=1")

In a similar way I wish to count the occurences of February, where the 'J' column range contains a 1.... but Excel doesnt like this....any idea how I could that please..?
 
Again use Sumproduct

Sumproduct((month(b6:b2057)=2)*(j6:j2057=1))

Give or take the odd syntax error as it's been a few years.

Brian
 

Users who are viewing this thread

Back
Top Bottom