hi biff,
the syntax you gave is working. thanks a lot and i highly appreciate it!
best regards,
vlad
"T. Valko" wrote:
> >based on your syntax given, this is an array right?
>
> Yes and no.
>
> It's a formula that works on arrays but it doesn't have to be array entered
> (CTRL, SHIFT, ENTER) but it'll work either way.
>
> It COUNTS the instances where G2:G8000 = 12:00 PM and Y2:Y8000 <180000
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "vjmc" <(E-Mail Removed)> wrote in message
> news:5F524882-60A1-4BD2-B6BA-(E-Mail Removed)...
> > hi biff,
> >
> > thanks for the feedback. what i am doing is, i am trying to filter out a
> > system generated statistics file in 30min intervals for 24hours. based on
> > your syntax given, this is an array right?
> >
> > vlad
> >
> > "T. Valko" wrote:
> >
> >> What does "12:00" represent? 12:00 AM or 12:00 PM ? If it's PM try this:
> >>
> >> =SUMPRODUCT(--(G2:G8000=TIME(12,0,0)),--(Y2:Y8000<180000))
> >>
> >> Note that if a cell in Y2:Y8000 is empty it will evaluate as being
> >> <180000
> >> and could lead to incorrect results.
> >>
> >> You can account for that by testing to make sure the cells do contain
> >> numbers:
> >>
> >> =SUMPRODUCT(--(G2:G8000=TIME(12,0,0)),--(ISNUMBER(Y2:Y8000)),--(Y2:Y8000<180000))
> >>
> >> You can also shorten it a little and replace TIME(12,0,0) with 0.5. 12:00
> >> PM
> >> is equivalent to decimal 0.5.
> >>
> >> =SUMPRODUCT(--(G2:G8000=0.5),--(ISNUMBER(Y2:Y8000)),--(Y2:Y8000<180000))
> >>
> >>
> >> --
> >> Biff
> >> Microsoft Excel MVP
> >>
> >>
> >> "vjmc" <(E-Mail Removed)> wrote in message
> >> news:C383C51E-E94D-46BA-8C8D-(E-Mail Removed)...
> >> > hi all,
> >> >
> >> > i am trying to duplicate the example in microsoft help about the
> >> > count(if((,,,)) function in my spreadsheet but it gives me false
> >> > results.
> >> > below is the syntax i am using:
> >> >
> >> > {=COUNT(IF((G2:G8000="12:00")*('Y2:Y8000<"180000"),Y2:Y8000))}
> >> >
> >> > i am wondering if in my first argument G2:G800="12:00" would be
> >> > processed
> >> > as
> >> > i am trying to filter out my data with respect to time? i tried
> >> > substituting
> >> > text within that column and the syntax gave me the needed results.
> >> >
> >> > {=COUNT(IF((G2:G8000="test")*('Y2:Y8000<"180000"),Y2:Y8000))} = 3 (i
> >> > put
> >> > 3
> >> > entries of different values less than 180000)
> >> >
> >> >
> >> > so, bottom line, will using "time" as part of the syntax will work in
> >> > this
> >> > particular scenario? thanks in advance...
> >> >
> >> > vjmc
> >>
> >>
> >>
>
>
>
|
Similar Threads
