Please help with Countif function

Row Col C Col D
1 Level 2 6:30 Pm
2 Level 1 12:30 Pm
3 Level 2 12:30 Pm
4 Level 1 6:30 Pm
5 Level 1 12:30 Pm
6 Level 2 6:30 Pm



I need to create a function as follows where both conditions must be true, however the second argument is not being picked up. I have reformatted this function every way I could think of resulting in either a format error or invalid results.

=COUNTIF(C1:C6,”Level 1”)and(D1:D6,”12:30 Pm”)

Expected results: 2

=COUNTIF(C1:C6,”Level 1”)and(D1:D6,”6:30 Pm”)

Expected results: 1

Hi!

Try

=SUMPRODUCT(--(C1:C6="Level 2")*(--(D1:D6=D2)))

The D2 in the last conditoin extracts the value (not the formatted "string" you see). Putting 12:30Pm won't work because the value is actually 0.52083333...

You might want to use a different reference cell (say, with a variable in).


Alf

Thank you very, very much it works.

Hi!

You're welcome!

Alf