Count occurance of months

  • Thread starter Thread starter DaveC
  • Start date Start date
D

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
 
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.
 
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
 
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
 
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?
 
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
 
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.
 
Back
Top