Month Totals... (1 Viewer)

GUIDO22 · May 21, 2015

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

namliam · May 21, 2015

try using the format function ...

GUIDO22 · May 21, 2015

namliam said:
try using the format function ...

Any chance of a sample showing the use of FORMAT over a range of cells please...?

namliam · May 21, 2015

Well you cant use functions for ranges... you first make a month per line.
Then use a sumif or cross tab to make totals.

Rx_ · May 21, 2015

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?

GUIDO22 · May 22, 2015

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

!

GUIDO22 · May 26, 2015

=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..?

Brianwarnock · May 26, 2015

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

GUIDO22 · May 26, 2015

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

Right you are, thank you Brian.

Month Totals... (1 Viewer)

GUIDO22

Registered User.

namliam

The Mailman - AWF VIP

GUIDO22

Registered User.

namliam

The Mailman - AWF VIP

Rx_

Nothing In Moderation

Attachments

GUIDO22

Registered User.

GUIDO22

Registered User.

Brianwarnock

Retired

GUIDO22

Registered User.

Similar threads

Users who are viewing this thread