Is there a way in which I can use countifs function to count cells that
contain a time between 09:00 and 17:00?

If not is there any other function I can use for this

Thanks

John

You can use sumproduct

=SUMPRODUCT((A1:A23=TIME(9,0,0))*(A1:A23<=TIME(17 ,0,0)))

Mike

"JRD" wrote:

Is there a way in which I can use countifs function to count cells that
contain a time between 09:00 and 17:00?

If not is there any other function I can use for this

Thanks

John

Unfortunately doen't quite work for me, as need to count only cells that have
time between 9am and 17pm, but also meet speciific criteria from same row in
another column.

Do you know of any other ways round this

John

"Mike H" wrote:

You can use sumproduct

=SUMPRODUCT((A1:A23=TIME(9,0,0))*(A1:A23<=TIME(17 ,0,0)))

Mike

"JRD" wrote:

Is there a way in which I can use countifs function to count cells that
contain a time between 09:00 and 17:00?

If not is there any other function I can use for this

Thanks

John

Hi,,

Sumproduct is still the correct formula

=SUMPRODUCT((A1:A23=TIME(9,0,0))*(A1:A23<=TIME(17 ,0,0))*(B1:B23="My other
condttion"))

Substitute "My Other condition2 with whatever you want

Mike

"JRD" wrote:

Unfortunately doen't quite work for me, as need to count only cells that have
time between 9am and 17pm, but also meet speciific criteria from same row in
another column.

Do you know of any other ways round this

John

"Mike H" wrote:

You can use sumproduct

=SUMPRODUCT((A1:A23=TIME(9,0,0))*(A1:A23<=TIME(17 ,0,0)))

Mike

"JRD" wrote:

Is there a way in which I can use countifs function to count cells that
contain a time between 09:00 and 17:00?

If not is there any other function I can use for this

Thanks

John

Using COUNTIFS for just the time range:

C1 = lower time boundry = 9:00
D1 = upper time boundary = 17:00

=COUNTIFS(A1:A100,"="&C1,A1:A100,"<="&D1)

but also meet speciific criteria from same row in another column.

It depends on what that condition is. COUNTIFS can only do "straight
comparrisons".


--
Biff
Microsoft Excel MVP


"JRD" wrote in message
...
Unfortunately doen't quite work for me, as need to count only cells that
have
time between 9am and 17pm, but also meet speciific criteria from same row
in
another column.

Do you know of any other ways round this

John

"Mike H" wrote:

You can use sumproduct

=SUMPRODUCT((A1:A23=TIME(9,0,0))*(A1:A23<=TIME(17 ,0,0)))

Mike

"JRD" wrote:

Is there a way in which I can use countifs function to count cells that
contain a time between 09:00 and 17:00?

If not is there any other function I can use for this

Thanks

John

Hi,

Since you are using 2007 try this

=COUNTIFS(A1:A4,"="&9/24,A1:A4,"<="&17/24)

and add your other condtions
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"JRD" wrote:

Is there a way in which I can use countifs function to count cells that
contain a time between 09:00 and 17:00?

If not is there any other function I can use for this

Thanks

John