Countif Formula while excluding items based off of another column?

Options

Hi!

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,

Annie

Answers

  • Paul H
    Paul H ✭✭✭✭✭✭
    Options

    You need to use COUNTIFS to add a second condition


  • AnnieR
    AnnieR ✭✭
    Options

    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
    Options

    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!