Countif Formula while excluding items based off of another column?

AnnieR

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

Paul H

You need to use COUNTIFS to add a second condition

https://help.smartsheet.com/function/countifs

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

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.