How to use countif in different criteria

Hi All,

Below is my table:


T F S S M T W T F S S M T W T F S S M T W T F S S M T W T F S
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 Total Total Total Total Total
james L L L AL L , 1/2MC L PH PH L L L L 8.0 1.0 0.0 0.0 0.0
kent AL PH AL PH MC 0.0 2.0 0.0 1.0 0.0
amy AL PH AL PH AL 0.0 3.0 0.0 0.0 0.0
jackson MC MC AL PH AL PH AL MC AL AL 0.0 5.0 0.0 3.0 0.0


I wish to know how many days they are L, so I use =COUNTIF(D5:AH5,"L") for it. For MC, I used =COUNTIF(D5:AH5,"MC")+COUNTIF(D5:AH5,"MC1/2")/2.
But there have a problem now, this formula couldn't capture James Late and 1/2 day MC on 9th.
Please assist.

In future I suggest you attach a workbook to your post (the FAQ describes how), as it is very difficult to follow what you have posted.

You can use wildcards with COUNTIF, so these should work for you:

=COUNTIF(D5:AH5,"L*")

=COUNTIF(D5:AH5,"MC")+COUNTIF(D5:AH5,"*MC1/2")/2

Hope this helps.

Pete

Hi Pete,

This is totally HELPFUL!
Thanks!!!

p/s:I'm newbie to be here, will be attach the workbook next time. Thanks!

Originally Posted by whlau

p/s:I'm newbie to be here, will be attach the workbook next time. Thanks!

Glad to be able to help. If you are satisfied that your problem has been answered, you should mark your thread as Solved - click on Thread Tools above your first post. Also, you can pass on thanks more directly to any contributor who has helped you by clicking on the "star" icon in the bottom left corner of any post that you have found to be helpful.

Note also that you can't do this in your situation:

=COUNTIF(D5:AH5,"*L*")

as it would count AL as being the same as L, but you could do this:

=COUNTIF(D5:AH5,"MC")+COUNTIF(D5:AH5,"*MC1/2*")/2

so that you could count the MC1/2 wherever it appeared in the cell.

Hope this helps.

Pete