Excel/Count if two variables are met
QUESTION: Hello again Jan, its been awhile. I need some help with the count function. The following is what my sheet looks like and an example of what I need in cells for each shift at the bottom of my name column, with what results I hope to get.
4/1 4/2 4/3
Raey, Cheryl LPN 6P 12P 12P
Ra id, Sandy LPN D D 12D
Br er, Denise DA 12P 12P 12P
Jos on, Darrell DA 7D 7D 7D
Sma n, Doug DA 12D D D
Hais, Ed RN 7D 4P noon
Bc h, Connie LPN noon 6D 7D
Carell, Boyd LPN ML 7P 7D
Fi sh, Ray LPN D 4D 4D
Bes, Ron DA D 7D 7D
Bu s, Joshua RN 12D 4D 4P
Partt, Felicia RN D 7D 7P
Mennhall, John LPN 4D D 4D
Loe back, Rob DA 4P D D
Steens, Willa DA D 4P 4P
Night Nurses assigned "results" For each day of the month.
2nd shift Nurses assigned "results"
Day shift nurses assigned "results"
4/1 for Night shift; If RN or LPN (AND) any of the following: 12p or 12d then count 1 for each Nurse and put total in cell. Exp. First column (4/1) “1” 2nd column (4/2)“1” 3rd column (4/3)“2”
Row for For day shift; If RN or LPN (AND) any of the following: 6p, 6d, noon, 7d, 7p then count 1 for each Nurse and put total in cell. Exp. First column (4/1) “2” 2nd column (4/2)“2” 3rd column (4/3)“4”
Row for For 2nd shift; If RN or LPN (AND) any of the following: noon (count for day and 2nd shifts), 4d, 4p then count 1 for each Nurse and put total in cell. Exp. First column (4/1) “3” 2nd column (4/2) “3” 3rd column (4/3) “4”.
I hope this is a good exp. and description. In a nutshell I want to count the number of nurses assigned for each day. a D, ML, AL, MEET, basically anthing with a number or noon should count if thier is an LPN or RN scheduled.
ANSWER: Hi John,
Suppose we're just looking at column D:
Counts the cells which have either 12P or 12D.
---------- FOLLOW-UP ----------
QUESTION: Hello Jan and thank you again. As always you are prompt and spot on. I have a follow-up question which is, I need to count as in your above solution but only if column A3:A19 have LPN or RN at the end of the name designation (Smith, Sam LPN). I want to count how many RN/LPN on duty each day, this of course coupled with your solution. I have tried the countif and sumproduct but can't seem to get it to count only once if both criteria are met. It counts once for your solution and then once for the RN or LPN occurrence. As written it counts all 12d and 12p and I don't want the DA counted on daily nurse count as they are not nurses.
The combination of names and LPN's and such makes this a hard puzzle to solve.
I inserted an extra column B in which I put the LPN and RN denotations. Then this formula solves your problem: