Countifs Formula
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")))
Best Answer
-
@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))
Answers
-
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.
-
@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))
-
I just got it figured out! :) I used: =COUNTIFS({LaunchYear}, "2021", {Approval Column}, "Approved", {Project Tracker Range 1}, Title18, {status}, "Open")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!