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

Try
=SUMPRODUCT(--(MONTH('Tracker 2010'!T6:T2643)=1))
--
If this helps, please click "Yes"
<<<<<<<<<<<


"DaveC" wrote:

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

Didn't work, got a #VALUE result

"BSc Chem Eng Rick" wrote:

Try
=SUMPRODUCT(--(MONTH('Tracker 2010'!T6:T2643)=1))
--
If this helps, please click "Yes"
<<<<<<<<<<<


"DaveC" wrote:

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

Hi DaveC,

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

"DaveC" wrote in message
...
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

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

--
Best Regards,

Luke M
"DaveC" wrote in message
...
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.
--
If this helps, please click "Yes"
<<<<<<<<<<<


"DaveC" wrote:

Didn't work, got a #VALUE result

"BSc Chem Eng Rick" wrote:

Try
=SUMPRODUCT(--(MONTH('Tracker 2010'!T6:T2643)=1))
--
If this helps, please click "Yes"
<<<<<<<<<<<


"DaveC" wrote:

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

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

"BSc Chem Eng Rick" wrote:

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.
--
If this helps, please click "Yes"
<<<<<<<<<<<


"DaveC" wrote:

Didn't work, got a #VALUE result

"BSc Chem Eng Rick" wrote:

Try
=SUMPRODUCT(--(MONTH('Tracker 2010'!T6:T2643)=1))
--
If this helps, please click "Yes"
<<<<<<<<<<<


"DaveC" wrote:

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

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" wrote:

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

--
Best Regards,

Luke M
"DaveC" wrote in message
...
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







.

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?

--
Best Regards,

Luke M
"DaveC" wrote in message
...
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" wrote:

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

--
Best Regards,

Luke M
"DaveC" wrote in message
...
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







.

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



"Luke M" wrote:

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?

--
Best Regards,

Luke M
"DaveC" wrote in message
...
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" wrote:

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

--
Best Regards,

Luke M
"DaveC" wrote in message
...
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







.



.

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.

DaveC wrote:

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

"Luke M" wrote:

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?

--
Best Regards,

Luke M
"DaveC" wrote in message
...
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" wrote:

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

--
Best Regards,

Luke M
"DaveC" wrote in message
...
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







.



.


--

Dave Peterson