COUNT(IF((,,,)) function

  • Thread starter Thread starter vjmc
  • Start date Start date
V

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
 
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))
 
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
 
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
 
hi biff,

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

best regards,

vlad
 
Back
Top