if/countif

[kelvin_9]

​hello all, can i use just 1 formula to return my request if i want to calculate how many staffs will work before 13:00 / after 13:00?
PS:
use M=Morning on before 13:00
use N=Night on after 13:00
if cell blank, no return, no count

=IF((LEFT(A2,5)<"13:00"),"M","N")
=COUNTIF(B2:B6,"M")

time	before / after 13:00
14:15 - 22:45	N
10:00 - 19:30	M
13:00 - 20:30	N
15:30 - 22:45	N

<tbody>
</tbody>

M	1
N	3

<tbody>
</tbody>

thanks,
kelvin

 

[Special-K99]

Try

=SUMPRODUCT(--(LEFT(A2:A6,5)+0 < TIMEVALUE("13:00"))

You cant use LEFT(range...) in a COUNTIF hence the SUMPRODUCT

 

[kelvin_9]

Try

=SUMPRODUCT(--(LEFT(A2:A6,5)+0 < TIMEVALUE("13:00"))

You cant use LEFT(range...) in a COUNTIF hence the SUMPRODUCT

brilliant special-k99
however, how can i skip the blank cell if there have 1 or more blank cell in a column?
ps: result turns to #value

 

[FormR]

Hi, here is another option you can try:

Excel 2013/2016
	A	B
1	time	before / after 13:00
2	14:15 - 22:45	N
3	10:00 - 19:30	M
4
5	13:00 - 20:30	N
6	15:30 - 22:45	N
7
8	M	1
9	N	3
Sheet1

Cell Formulas
Range	Formula
B8	=COUNTIFS($A$2:$A$6,"<13:00 - 00:00",$A$2:$A$6,"??:?? - ??:??")
B9	=COUNTIFS($A$2:$A$6,">=13:00 - 00:00",$A$2:$A$6,"??:?? - ??:??")

 

[Special-K99]

The #VALUE error
Aww, its cos the range is a mixture of text (blank rows) and numbers, SUMPRODUCT doesnt like a range that doesnt include all numbers

Deleting the blank row results in a number not a #VALUE error.

 

[kelvin_9]

what if I can't deleting any blank cell in a column?
The column I return my team's schedule, and final step would be easier to check out how many M/N over 100 staff in a column
Hope this can be fixed

Thanks special-k99

 

[FormR]

what if I can't deleting any blank cell in a column?

Hi, did you try the suggestion in post#5?

 

[kelvin_9]

formR
I apologize for the mistake
Your suggestion is brilliant and exactly what I want
thank you very much