Counting data for a particular month

[BigMac]

Hi there!

I have two columns of data, first one would be my contract number and
second one would be the date of completion of the contract. How do I go
about using summing up the number of contracts I've completed in a
particular month? For example:


Contract Number Date
------------------------ --------
27 1/11/2006
87 2/8/2006
76 1/1/2006
45 4/8/2006
78 9/12/2006
41 2/1/2006
08 9/6/2006
.. .


So from the above, I need the number of contracts I've done for the
month of January, February, so on and so forth. I'm kind of stuck with
dates for EXCEL.


Yup that's the general idea. Thanks!

Regards
Maccann Yeo

 

[Dave Peterson]

One way to count the number of contracts done in January of 2006:

=sumproduct(--(text(b1:b100,"yyyymm")="200601")

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

But if you have lots of months to check, you may want to look into
data|Pivottable. You can group the dates by month and year and get a count
pretty easily.

 

[BigMac]

Hi. So sorry, but I think I conveyed the wrong idea due to the wrong
wording used.

My purpose is to actually count the number of contracts that were
completed in the month of January, February and so on, not really the
sum.

For instance, I'd want to know that the number of contracts that took
place in January was 2 (The format is in the ddmmyyyy format),
February: 0, June: 1, August: 2 and so on

Sorry for the confusion!

 

[Roger Govier]

Hi
Dave had a slight typo in his formula. He meant to type
=SUMPRODUCT(--(TEXT(B1:B100,"yyyymm")="200601"))

It will give you a count of the number of contracts completed in the
month of January 2006
Don't take any notice of the fact that it is Sumproduct being used, it
will in this instance produce a count, not a Sum.
Each test will return True or False. The double unary minus -- coerces
True's to 1's and False's to 0's, which Sumproduct then sums to give you
the total number of matches.

Alternatively you could use
=SUMPRODUCT(--(MONTH(B1:B100)=1)) for January, 2 for February etc.
but be aware that if you had more than one year's worth of data, it
would add January 06 and January 07 results together.
Dave's method takes account of Year as well as month.

--
Regards

Roger Govier

Hi. So sorry, but I think I conveyed the wrong idea due to the wrong
wording used.

My purpose is to actually count the number of contracts that were
completed in the month of January, February and so on, not really the
sum.

For instance, I'd want to know that the number of contracts that took
place in January was 2 (The format is in the ddmmyyyy format),
February: 0, June: 1, August: 2 and so on

Sorry for the confusion!

 

[Dave Peterson]

Thanks for the correction, Roger (but excel would have corrected it, too <bg>.)

And just another warning...

=SUMPRODUCT(--(MONTH(B1:B100)=1))
will count empty cells as January, too.

I'd add a check:
=SUMPRODUCT(--(MONTH(B1:B100)=1),--ISNUMBER(B1:B100))

Hi
Dave had a slight typo in his formula. He meant to type
=SUMPRODUCT(--(TEXT(B1:B100,"yyyymm")="200601"))

It will give you a count of the number of contracts completed in the
month of January 2006
Don't take any notice of the fact that it is Sumproduct being used, it
will in this instance produce a count, not a Sum.
Each test will return True or False. The double unary minus -- coerces
True's to 1's and False's to 0's, which Sumproduct then sums to give you
the total number of matches.

Alternatively you could use
=SUMPRODUCT(--(MONTH(B1:B100)=1)) for January, 2 for February etc.
but be aware that if you had more than one year's worth of data, it
would add January 06 and January 07 results together.
Dave's method takes account of Year as well as month.

 

[Roger Govier]

Absolutely right Dave, as always <vbg>

 

[Dave Peterson]

You have to read more of my posts and you'll be busier with corrections! <bg>

 

[BigMac]

Hey guys, I've managed to get it to work. Thank you so much!

Regards
Maccann