Total rows in specific column excluding extra column if checked


(Please see screenshot for reference)

I am trying to get a "total locations" excluding if "cancelled" is checked. Current formula in "total locations is.

=COUNT([Location #]:[Location #])

This gives the total of every row in the "Location #" column, but I need to show the total of what is left if one is marked "cancelled". Can anyone help?

Looking for something similar in the below screenshot. Basically I need to exclude "cancelled" in each of these.

"month" only counts locations if "cancelled" is not checked. Same for the part #'s.

Best Answer


  • Thanks Genevieve. The first one worked. The other two suggestions gave an error. I'm including screenshots of the exact headings I have. This might help.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi Beth,

    Yes, you will need to update the column names. Try this for your second one:

    =COUNTIFS([Date Scheduled]:[Date Scheduled], MONTH(@cell) = 1, Cancelled:Cancelled, <> 1)

    Make sure that when you're referencing a column the name in between [these brackets] lights up in a specific colour (such as pink, purple, etc). This will indicate that the formula has found the column you are looking to reference.

    For your third one, I'm not quite sure I understand where your "Part Number" column is. Are all of those different columns Part Numbers that you want totalled? If so, you would just need to adjust the first formula to have the Column Name in the first range (bolded below):

    =COUNTIFS([108-020]:[108-020], <> "", Cancelled:Cancelled, <> 1)

    Hope that helps!


  • Those worked perfect! Thanks so much for the help.

  • Genevieve P.
    Genevieve P. Employee Admin

    Wonderful! Glad to have helped.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!