Prev: conditional formatting for more than 3 conditions
Next: Final Reminder - Microsoft Responds to the Evolution of Community
From: Dave Peterson on 30 May 2010 07:07
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" <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
> > >> >
> > >> >
> > >> >
> > >>
> > >>
> > >>
> > >>
> > >> .
> > >>
> >
> >
> > .
> >

--

Dave Peterson
First  |  Prev  | 
Pages: 1 2 3
Prev: conditional formatting for more than 3 conditions
Next: Final Reminder - Microsoft Responds to the Evolution of Community