COUNTIF, MONTH, YEAR

[JoeyJoey]

Hi there

I have a table of client data including the date that they first paid for
services. I would like to summarise the information by counting the number of
new clients per month (or, prefereably by week but I realise Excel doesn't
have a WEEK formula) and returning the information in a new table with a list
of months.

It seems that the issue is in counting if the date from the client data
table lies within the bounds of a particular month in a particular year. I
have tried the MONTH and YEAR formulas but can't get them to work together
while counting the number of times both are TRUE.

Help would be very gratefully received!

Thanks

 

[Bernard Liengme]

Here is my test data in A1:A10
01/02/2006
05/06/2007
12/08/2007
13/03/2008
15/03/2008
16/10/2008
17/01/2009
20/04/2009
22/07/2009
23/10/2009


Here is part of my result
------- 1 2 3
2006 0 1 0
2007 0 0 0
2008 0 0 2
2009 1 0 0

The months ( 1 thri 12) run from E1 to P1. the years (2006 to 2009) from
D25
The formula in E2 which is copied to fill the table is
=SUMPRODUCT(--(MONTH($A$1:$A$10)=E$1),--(YEAR($A$1:$A$10)=$D2))


For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html
Debra Dalgleish
http://www.contextures.com/xlFunctions04.html#SumProduct


Another approach would be a pivot table
Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx

 

[Ron Rosenfeld]

Hi there

I have a table of client data including the date that they first paid for
services. I would like to summarise the information by counting the number of
new clients per month (or, prefereably by week but I realise Excel doesn't
have a WEEK formula) and returning the information in a new table with a list
of months.

It seems that the issue is in counting if the date from the client data
table lies within the bounds of a particular month in a particular year. I
have tried the MONTH and YEAR formulas but can't get them to work together
while counting the number of times both are TRUE.

Help would be very gratefully received!

Thanks

If you could post a sanitized example of your data, and of how you want to see
the reports, it should be fairly simple to devise an analytic method.

You could even group by weeks, if you like.

For example, using Bernard's data list, and a Pivot table, I can generate this
report:

Weeks New per Week
2/1/2006 - 2/7/2006 1
5/30/2007 - 6/5/2007 1
8/8/2007 - 8/14/2007 1
3/12/2008 - 3/18/2008 2
10/15/2008 - 10/21/2008 1
1/14/2009 - 1/20/2009 1
4/15/2009 - 4/21/2009 1
7/22/2009 - 7/28/2009 1
10/21/2009 - 10/24/2009 1

Grand Total 10
--ron

 

[Chip Pearson]

You can use the SUMPRODUCT to do this. For example, assume your dates
are in A1:A30. In C1:C12 enter 1,2,3,...12 for the month numbers. In
F1, enter the year, say 2009. Then in D1, enter

=SUMPRODUCT((MONTH(A$1:A$30)=C1)*(YEAR(A$1:A$30)=F$1))

Copy this formula down to fill D112. The results will be the number
of dates in A1:A30 that are in the month specified in column C in the
year specified in F1. The $ characters are required as shown in the
formula above.

A variation on this theme would be to put the series 1,2,3,...12 in
column B, repeating the group for as many years as you want to look
at. Then, in column C, enter groups of year numbers, 12 elements of
2008, followed by 12 elements of 2009, etc, so that the values in B
and C specify a specific month and year. The sequence of months and
years would be something like

[Col B] [Col C]
1 2007
2 2007
3 2007
.....
1 2008
2 2008
3 2008
.....
1 2009
2 2009
.....

Then in D1, enter
=SUMPRODUCT((MONTH(A$1:A$30)=B1)*(YEAR(A$1:A$30)=C1))
and copy down as far as you have months and years in column B and C.
As before, you need the $ characters as shown.

If you just want a formula to return the count of dates in a single
specified month and year, put the month number in G1 and the year in
H1 and use the formula

=SUMPRODUCT((MONTH(A$1:A$30)=G1)*(YEAR(A$1:A$30)=H1))

This will return the number of dates in A1:A30 whose month is G1 and
year is H1.

Similar formulas could be written to handle weeks rather than months,
but you get into the question of what is meant by a week number.
Depending on interpretation and convention, week 1 could be the week
starting on 1-Jan, regardless of the day of week, or week 1 could
start on the first Sunday of the year, or week 1 could be the the week
that contains the first Thursday of the year. The is an ISO standard
for week numbers, but depending on the year, you may have days in a
year that are earlier than week 1. Moreover, not everyone follows the
ISO standard. In general, I try to steer clients away from using week
numbers because of the ambiguity in what a week number really means.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Chipgiii]

A handy formula to create week ending dates ( that you can then pivot for
summation purposes is: assuming column dates are in A2, A3,....
=A2+7-weekday(A2,2)
this gives you a Sunday week ending date. You can change the "7" to 6 for
Sat, 5 for Fri, etc...if you prefer a different week ending day.

Date Week Ending DOW Week Ending DOW
40179 =A4+7-WEEKDAY(A4,2) =TEXT(A4,"ddd") =TEXT(B4,"ddd")
40180 =A5+7-WEEKDAY(A5,2) =TEXT(A5,"ddd") =TEXT(B5,"ddd")
40181 =A6+7-WEEKDAY(A6,2) =TEXT(A6,"ddd") =TEXT(B6,"ddd")
40182 =A7+7-WEEKDAY(A7,2) =TEXT(A7,"ddd") =TEXT(B7,"ddd")
40183 =A8+7-WEEKDAY(A8,2) =TEXT(A8,"ddd") =TEXT(B8,"ddd")
40184 =A9+7-WEEKDAY(A9,2) =TEXT(A9,"ddd") =TEXT(B9,"ddd")