Countifs Formula

Catherine Pauciello

I am very new to Smartsheets and having trouble with this formula. I'm sure it's something stupid like a " or a () but can anyone see what is wrong with this statement?

=COUNTIFS((({LaunchYear}, Products142021"), (AND{Phase, Project Tracker}, "Title9"), (AND{status}, "Open"), (AND{Approval Column}, "Approved")))

Paul Newcome

@Catherine Pauciello You only need the opening parenthesis after COUNTIFS and the closing parenthesis at the end. Additionally, the AND is implied with COUNTIFS meaning you do not need to include that function for the COUNTIFS to only count rows where all of those range/criteria sets are true.

=COUNTIFS({LaunchYear}, Products142021", {Phase, Project Tracker}, "Title9", {status}, "Open", {Approval Column}, "Approved")

@Shauna Brotz You are going to want an OR statement for your criteria with "@cell" references like so...

=COUNTIFS([Q1.]2:[Q1.]1000, OR(@cell = 10, @cell = 9))

Shauna Brotz

I am actually having a similar issue using =COUNTIFS. I have two criterion for the same range, but cannot get both criterion to work together. If I write the formula for each one separately, I get the correct numbers, but cannot get both to work together. Can anyone tell me how to merge the two together to get them to count all the 10s and 9s within the range?

=COUNTIFS([Q1.]2:[Q1.]1000, 10)

=COUNTIFS([Q1.]2:[Q1.]1000, 9)

Regarding your formula, I am not understanding all of the use of parenthesis...I was taught if you open a range, criterion and then close it, that portion is complete. Also, you forgot the " in front of Products142021. Lastly, when looking at trying to use multiple criterion in Smartsheet, it appears that instead of { } you should be using [ ]. Hope that tidbit helps.

Paul Newcome

@Catherine Pauciello You only need the opening parenthesis after COUNTIFS and the closing parenthesis at the end. Additionally, the AND is implied with COUNTIFS meaning you do not need to include that function for the COUNTIFS to only count rows where all of those range/criteria sets are true.

=COUNTIFS({LaunchYear}, Products142021", {Phase, Project Tracker}, "Title9", {status}, "Open", {Approval Column}, "Approved")

@Shauna Brotz You are going to want an OR statement for your criteria with "@cell" references like so...

=COUNTIFS([Q1.]2:[Q1.]1000, OR(@cell = 10, @cell = 9))

Catherine Pauciello

I just got it figured out! :) I used: =COUNTIFS({LaunchYear}, "2021", {Approval Column}, "Approved", {Project Tracker Range 1}, Title18, {status}, "Open")