Count occurance of months

[DaveC]

Hi Folks,

I am using the following formula to try to count how many cells contain a
date within January but its not working for me

=SUMPRODUCT((MONTH('Tracker 2010'!T6:T2643)=1)*('Tracker 2010'!T6:T2643<>" "
))

Date format is 13/01/2010 in T6, 16/01/2010 in T7 and so on

Any help you could give is appreciated

Dave

 

[BSc Chem Eng Rick]

Try
=SUMPRODUCT(--(MONTH('Tracker 2010'!T6:T2643)=1))

 

[DaveC]

Didn't work, got a #VALUE result

Try
=SUMPRODUCT(--(MONTH('Tracker 2010'!T6:T2643)=1))

 

[steve]

Hi DaveC,

Try =SUMPRODUCT(--(MONTH('Tracker 2010'!T6:T2643)=1))

 

[Luke M]

=SUMPRODUCT(--(TEXT('Tracker 2010'!T6:T2643,"mm")="01"))

 

[BSc Chem Eng Rick]

Tried it again on my side an it worked fine. Make sure ALL the dates are
actually dates and that some of them are not text.

 

[DaveC]

I did it again and it gave me a a result of over 2,000. This is incorrect, it
should only be returning a value of 16 for Jan. I tried the same formula for
Feb with it changed to 2

=SUMPRODUCT(--(MONTH('Tracker 2010'!T6:T2643)=2))

and it gave me a result of 20 which is correct

I dont understand why its not working for Jan

 

[DaveC]

Nope not working for Jan

I have got the following formula working for every month except Jan, I dont
whats going on

=SUMPRODUCT(--(MONTH('Tracker 2010'!$Q$6:$Q$2643)=2),--('Tracker
2010'!$D$6:$D$2643="Whyte"))

I'm banging my head against a wall here

 

[Luke M]

Well, the problem appears to be that somewhere you have a cell with text in
Q6:Q2643. If the MONTH function encounters this, it spits out the #VALUE
error. This is why I suggested the TEXT function, as it will simply ignore
non-date values.

Was the formula I gave giving an error, or simply not the result you
expected?

 

[DaveC]

Hi Luke,

No matter what version I use I am getting incorrect results for Jan but
correct infomation for all other months. These are the 2 versions I have used
so far based on the help from you and other here

=SUMPRODUCT(--(TEXT('Tracker 2010'!$Q$6:$Q$2643,"mm")="01"),--('Tracker
2010'!$D$6:$D$2643="Whyte"))

and

=SUMPRODUCT(--(MONTH('Tracker 2010'!$Q$6:$Q$2643)=2),--('Tracker
2010'!$D$6:$D$2643="Whyte"))

Both of these return a result of 30. The correct result should be 16 for 16
occurances in Jan

Any help you or others could giove is appreciated

Thanks,
Dave

 

[Dave Peterson]

I'm assuming that the second example should have compared the month() to 1 (for
January).

Try:

=SUMPRODUCT(--(MONTH('Tracker 2010'!$Q$6:$Q$2643)=1),
--(isnumber('tracker 2010'!$q$6:$q$2643)),
--('Tracker 2010'!$D$6:$D$2643="Whyte"))

If A1 is an empty cell, then
=month(A1)
will return 1.

So if any of those cells in Q6:Q2643 are empty, then they will be counted as
January, too.