Need help Summary Formula Countif multiple criteria

Have a formula - Issue is that the fit/gap can be one or the other not both
=(Countif([Client Final Approval]:[Client Final Approval], “Needs Review”))+ (Countif([Fit/Gap]:[Fit/Gap], “Gap – In Scope”)) + (Countif([Fit/Gap]:[Fit/Gap], “Fit – In Scope”))
________________________________________________________________________________________________________________________
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
Meg Young | Smartsheet Consultant mmyoungconsulting@gmail.com. Certified in Core Product, Project Management, and System Administration
Best Answer
-
You're capturing rows that meet all of that criteria and adding them together.
Maybe you are trying to combine the Client Final Approval with the Fit/Gap criterion?
If so, use CountIFS()
Countifs([Client Final Approval]:[Client Final Approval], “Needs Review”, [Fit/Gap]:[Fit/Gap], “Gap – In Scope”) + Countifs([Client Final Approval]:[Client Final Approval], “Needs Review”, [Fit/Gap]:[Fit/Gap], “Fit – In Scope”)
ALTERNATIVELY
Countifs([Client Final Approval]:[Client Final Approval], “Needs Review”, [Fit/Gap]:[Fit/Gap], or(@cell = “Gap – In Scope”, @cell = “Fit – In Scope”))
Darren Mullen - Looking to take your Smartsheet knowledge to the next level and become an expert? Join the Smartsheet Guru Elite
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
Answers
-
You're capturing rows that meet all of that criteria and adding them together.
Maybe you are trying to combine the Client Final Approval with the Fit/Gap criterion?
If so, use CountIFS()
Countifs([Client Final Approval]:[Client Final Approval], “Needs Review”, [Fit/Gap]:[Fit/Gap], “Gap – In Scope”) + Countifs([Client Final Approval]:[Client Final Approval], “Needs Review”, [Fit/Gap]:[Fit/Gap], “Fit – In Scope”)
ALTERNATIVELY
Countifs([Client Final Approval]:[Client Final Approval], “Needs Review”, [Fit/Gap]:[Fit/Gap], or(@cell = “Gap – In Scope”, @cell = “Fit – In Scope”))
Darren Mullen - Looking to take your Smartsheet knowledge to the next level and become an expert? Join the Smartsheet Guru Elite
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
-
Hi @Meg Y
You can use the OR(@cell=A,@cell=B) format.
=COUNTIFS([Client Final Approval]:[Client Final Approval], "Needs Review", [Fit/Gap]:[Fit/Gap], OR(@cell = "Gap – In Scope", @cell = "Fit – In Scope"))
Alternatively, you can use a helper column, as shown in the image below.
=OR([Fit/Gap]@row = "Gap – In Scope", [Fit/Gap]@row = "Fit – In Scope")
-
Both columns are dropdowns. The formula is also for gap - out of scope and fit - out of scope which are also in the fit/gap column
Client final approval dropdowns are Needs Review and Approved
The numbers are four different widgets on the dashboard. Goal is use the sheet summary.
________________________________________________________________________________________________________________________
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
Meg Young | Smartsheet Consultant mmyoungconsulting@gmail.com. Certified in Core Product, Project Management, and System Administration
-
@Darren Mullen Thank you this formula works. Figured out that one of my criteria was typed incorrectly. Fixed that and viola
=COUNTIFS([Client Final Approval]:[Client Final Approval], "Needs Review", [Fit/Gap]:[Fit/Gap], OR(@cell = "Gap – In Scope", @cell = "Fit – In Scope"))
________________________________________________________________________________________________________________________
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
Meg Young | Smartsheet Consultant mmyoungconsulting@gmail.com. Certified in Core Product, Project Management, and System Administration
Help Article Resources
Categories
Check out the Formula Handbook template!