COUNT(IF((,,,)) function
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" wrote in message
...
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
|