how to use COUNTM with exceptions

Dear Community,

Can someone please help me on the below formula?

I am using this formula to get the total tasks in column Total Task but I don't want to consider Hours element so if we select other elements along with the Hours the total should be counting all the other elements + number of addtional task except the Hours element.

=COUNTM([Task Type]@row) + ([Additional Task]@row)


Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Aatish Chaudhary

    There are a couple ways to do this! Since you're only looking for 1 value ("Hours") I would personally use an IF statement to say that IF that cell HAS "Hours", -1.

    Try this:

    =IF(HAS([Task Type]@row, "Hours"), COUNTM([Task Type]@row) - 1, COUNTM([Task Type]@row)) + [Additional Task]@row

    Cheers,

    Genevieve

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!