I need help with formulas!

nbacguy23

Hello, I am trying to create a dashboard for our staffing office and trying to create a visualization of how many of each discipline (RN, MHW) are assigned to each shift. I have a separate Metrics sheet where I have the total for each shift and the total for each Discipline; however, I cannot figure out the formula to calculate a total for each Discipline assigned to each Shift. I hope the screenshots help, and I greatly appreciate any feedback :)

Capture.PNG

Capture2.PNG

Courtney S.

Hi! Have you used the COUNTIFS function before?

COUNTIFS Function | Smartsheet Learning Center

I think this is what you want. Basically, instead of just giving the formula one range+criterion, you can give the formula multiple range+criterion pairs to look at. So for the first range+criterion pair you can use the exact same wording as your shift COUNTIF, but then for the next pair inside the COUNTIFS formula you enter the Discipline column & the Discipline value you want counted for that shift. Then the COUNTIFs formula should give you the total rows that meet both of those criteria.

Courtney S.

Hi! Have you used the COUNTIFS function before?

COUNTIFS Function | Smartsheet Learning Center

I think this is what you want. Basically, instead of just giving the formula one range+criterion, you can give the formula multiple range+criterion pairs to look at. So for the first range+criterion pair you can use the exact same wording as your shift COUNTIF, but then for the next pair inside the COUNTIFS formula you enter the Discipline column & the Discipline value you want counted for that shift. Then the COUNTIFs formula should give you the total rows that meet both of those criteria.

nbacguy23

Thank you very much, this worked! For reference, here is the formula I used:

=COUNTIFS({ReferenceColumn1}, "MHW", {ReferenceColumn2}, "3 - 11 PM to 7 AM")