countifs

[JRD]

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

 

[Mike H]

You can use sumproduct

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

Mike

 

[JRD]

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]

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

 

[T. Valko]

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

 

[Shane Devenshire]

Hi,

Since you are using 2007 try this

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

and add your other condtions