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


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

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

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" wrote in message
...
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" 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

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" wrote in message
...
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" 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

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"vjmc" wrote in message
...
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" wrote in message
...
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" 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