Issue with CountIfs and multiple OR() statements, and criteria

Options

I am hoping to get some help with a formula. I need to count based on multiple columns in a sheet, and only certain values in each of those columns. in the below example counting number of Demand for RN (staff type), and for only early start times, for Today().

This is what I have so far, I am thinking the multiple OR() statements may be causing this to return as count of "0", but I know I have rows that meet this criteria:

=COUNTIFS({Intake - Master Sheet (V.2) StaffType}, OR(@cell = "RN", @cell = "RN - Peds", @cell = "PICU Flex RN"), {Intake - Master Sheet (V.2) SupplyDemand}, "Demand", {Intake - Master Sheet (V.2) Shift}, OR(@cell = "AM (7A - 7P)", @cell = "DA (7A - 3P)", @cell = "D4 (7A - 11A)", @cell = "9a (9a-9p)"), {Intake - Master Sheet (V.2) Date}, @cell = TODAY())

Any help is much appreciated!

Answers

  • Samuel Mueller
    Samuel Mueller Overachievers
    Options

    @Jdecarlo7 try removing the date criteria temporarily to see if that gives you a count.

    =COUNTIFS({Intake - Master Sheet (V.2) StaffType}, OR(@cell = "RN", @cell = "RN - Peds", @cell = "PICU Flex RN"), {Intake - Master Sheet (V.2) SupplyDemand}, "Demand", {Intake - Master Sheet (V.2) Shift}, OR(@cell = "AM (7A - 7P)", @cell = "DA (7A - 3P)", @cell = "D4 (7A - 11A)", @cell = "9a (9a-9p)"))

  • Jdecarlo7
    Options

    @Samuel Mueller I removed the date field and there is now a count showing, but seems rather low (single digits) for the amount of data in the sheet (spans over multiple months). My aim is to have this metric show on a dashboard and change daily, for different shift times (AM, PM etc..) and different staff types (RN, Tech etc..).

  • Samuel Mueller
    Samuel Mueller Overachievers
    Options

    you have a lot of criteria's so a low count might make sense. Your formula with the today() piece should work. I would double check that your data actually matches your criteria.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!