You are here:

# Excel/Count if two variables are met

Question
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
Name
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.
Thank you.
John

Suppose we're just looking at column D:

=SUMPRODUCT(--((D3:D19="12P")+(D3:D19="12D")>0))

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.

Hi John,

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:

=SUMPRODUCT((((\$B4:\$B86="LPN")+(\$B4:\$B86="RN"))>0)*(((C4:C86="12P")+(C4:C86="12D"))>0))
Questioner's Rating
 Rating(1-10) Knowledgeability = 10 Clarity of Response = 10 Politeness = 10 Comment Perfect, Perfect, Perfect, works just the way you set it up. Thank you very much again Jan.

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

#### Jan Karel Pieterse

##### Expertise

Excel and Excel/VBA questions

##### Experience

Excel MVP

Organizations
Self employed Excel developer

Education/Credentials
Bachelor in Chemical Engineering

Awards and Honors
Microsoft MVP award since 2002

Past/Present Clients
Shell, Fortis bank, ABN-AMRO bank, Morgan Stanley, ...