COUNTIF For multiple Drop down values in Same Column

Tim Wing
Tim Wing ✭✭
edited 07/07/23 in Formulas and Functions

Hi. Hoping someone can guide me.

I have a formula in Calc sheet pulling from an Action Log template used in Control Centre, that originally only picked up one value and had an IFERROR Statement included and a minus figure to ignore what was in a placeholder row at the top.

That formula was =(IFERROR(COUNTIF({Action Log Range 1}, "Open"), 0)) - 1.

Now the referenced column has changed its drop down values to "In Progress", "Not Started" and "Complete". Therefore for 'open' actions I now need to count the "In Progress" and "Not Started" values. I can get it to work for one value "=(IFERROR(COUNTIF({5) Action Log Range 1}, "Not Started"), 0)) - 1", but when I try to add in the second count for "In Progress" it errors out.

Any pointers gratefully received.

Best Answer

  • L_123
    L_123 ✭✭✭✭✭✭
    Answer ✓

    =(IFERROR(COUNTIF({5) Action Log Range 1}, or(@cell = "In Progress",@cell = "Not Started"), 0)) - 1


    Your range name is a little wonky, I don't know if you made a typo or not, but if it's correct then this should work.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!