|
Count occurance of months
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 |
Count occurance of months
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 |
Count occurance of months
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 |
Count occurance of months
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 |
Count occurance of months
=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 |
Count occurance of months
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 |
Count occurance of months
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 |
Count occurance of months
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 . |
Count occurance of months
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 . |
Count occurance of months
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 . . |
Count occurance of months
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 |
| All times are GMT +1. The time now is 12:55 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com