Countif Formula while excluding items based off of another column?



I would like to have a countif formula, but I don't want smartsheets to count any items if "Hardware" is listed in a different column from my source one that I'm pulling from.

Here is my formula now:

=COUNTIF({Project1 _ Target Date xx/xx/xx Range 1}, Values@row) + COUNTIF({Project2 - Target Date xx/xx/xx Range 1}, Values@row) + COUNTIF({Project3 - Target Date xx/xx/xx Range 1}, Values@row) + COUNTIF({Project4 - Target Date xx/xx/xx Range 1}, Values@row) + COUNTIF({Project5 - Target Date 5/6/2022 Range 1}, Values@row) + COUNTIF({Project6 Range 1}, Values@row) + COUNTIF({Project7 Range 1}, Values@row) + COUNTIF({Project8 Range 1}, Values@row) + COUNTIF({Project9 Range 1}, Values@row) + COUNTIF({Project10 Range 1}, Values@row)

How would I add in something to say to return a zero value for the count if "Hardware" is listed in a separate column?

Any insight would be very much appreciated! :)

Thank you,



  • Paul H
    Paul H ✭✭✭✭✭✭

    You need to use COUNTIFS to add a second condition

  • AnnieR
    AnnieR ✭✭

    Thank you!

    .. something kind of like this? =COUNTIFS({Project1 _ Target Date xx/xx/xx Range 1}, Values@row, {Project1 _ Target Date 7/4/22 Range 1}, NOT(@cell = "Hardware")

    I'm getting a "nested criteria" error. I'm also confused why smartsheets doesn't seem to update the ranges when I use the "edit reference" button. I've felt like I've just gotten random ranges, or just always range 1 regardless of which range I actually picked.

  • Just_Chris
    Just_Chris ✭✭
    edited 03/25/22

    Hi Annie,

    You could add an automation to support your formula. (you said item should only be counted if "hardware" wasnt listed in non-source columns).

    1) Change-Cell-Value Automation>If,and columns x,zy are not one of: "hardware">>change cell value to "Calculate me" in ("Column 1"-example)

    2) IF SUM column is "YES", =sum>>select columns to sum>>>right click and change to column formula

    This as a very basic 1-2 step of what you could adapt to your sheet.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!