COUNT(IF((,,,)) function

[vjmc]

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

 

[T. Valko]

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))

 

[vjmc]

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]

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

 

[vjmc]

hi biff,

the syntax you gave is working. thanks a lot and i highly appreciate it!

best regards,

vlad

 

[T. Valko]

You're welcome. Thanks for the feedback!