Count occurance of months
in [General]
|
Prev: conditional formatting for more than 3 conditions
Next: Final Reminder - Microsoft Responds to the Evolution of Community
From: BSc Chem Eng Rick on 28 May 2010 13:43 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 > > > > > > > > >
From: DaveC on 28 May 2010 13:54 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 > > > > > > > > > > > >
From: DaveC on 28 May 2010 14:56 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" <DaveC(a)discussions.microsoft.com> wrote in message > news:9598C038-25E9-49B2-8C29-D35282F0EF61(a)microsoft.com... > > 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 > > > > > > > > > > > . >
From: Luke M on 28 May 2010 15:39 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" <DaveC(a)discussions.microsoft.com> wrote in message news:2F391D4C-FF0D-475A-B70D-5906C5E9E1E3(a)microsoft.com... > 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" <DaveC(a)discussions.microsoft.com> wrote in message >> news:9598C038-25E9-49B2-8C29-D35282F0EF61(a)microsoft.com... >> > 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 >> > >> > >> > >> >> >> >> >> . >>
From: DaveC on 30 May 2010 02:58
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" <DaveC(a)discussions.microsoft.com> wrote in message > news:2F391D4C-FF0D-475A-B70D-5906C5E9E1E3(a)microsoft.com... > > 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" <DaveC(a)discussions.microsoft.com> wrote in message > >> news:9598C038-25E9-49B2-8C29-D35282F0EF61(a)microsoft.com... > >> > 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 > >> > > >> > > >> > > >> > >> > >> > >> > >> . > >> > > > . > |