I am trying to figure out a COUNTIF with AND formula while referencing another sheet.


I am trying to create a formula that counts both if the base health is green, and if it is in shift 1. I will be doing this for all of them. If they are red in shift 1, yellow in shift 1, green in shift 2, red in shift 2, yellow in shift 2, green in shift 3, yellow in shift 3, and red in shift 3. Here is the exact part of that sheet I am referencing in this specific formula

I feel like I have tried every formula in the book! Any help would be appreciated.


Best Answer

  • SteyJ
    SteyJ ✭✭✭✭✭✭
    Answer ✓

    Try this:

    =COUNTIFS([Primary Column]:[Primary Column], "Shift 1", [Base Health]:[Base Health], "Green")

    or if it's referencing another sheet, do this

    =COUNTIFS({Primary Column}, "Shift 1", {Base Health}, "Green")


    Jacob Stey


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!