COUNTIFS for whole Sheet
Hey,
This is probably an easy fix but I am having no luck.
I want to count if anywhere on a sheet has a "4" in a cell.
I also want this to separate the counts by "ITCMW", "ITCT" & "METC"
My current formula is:
=COUNTIFS([Pre Trip Completed]:[Doors/Gates], HAS(@cell, "4"))
I need to include System:System, "ITCMW" in this formula.
Any thoughts?
Answers
-
Can you post some screenshots of your grid layout and sample data?
Are the below column names?
I also want this to separate the counts by "ITCMW", "ITCT" & "METC"
I'm not sure what you're saying here. Can you expand on this?
I need to include System:System, "ITCMW" in this formula.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Below your first question are not the column names. The column name would be "System" as shown in a screenshot.
I also attached a screenshot in an attempt to show I am trying to break this total count of "4's" down by the the categories within the System column. Those categories are ITCMW, ITCT and METC. Sorry for the confusion.
-
I think I get it now.
The COUNTIFS formula allows you to specify that multiple conditions must be met for the row to be counted. However the below will give you an #INCORRECT ARGUMENT SET error...
=COUNTIFS([Pre Trip Completed]:[Doors/Gates], HAS(@cell, "4"), [System]:[System], "ITCMW")
And breaking the [Pre Trip Completed]:[Doors/Gates] range into individual ranges will only count the rows where ALL the conditions are met, which is not what you want.
So I'm going to suggest adding a helper column to do some of the heavy lifting on a row by row basis, and then using the summary fields to finish the math.
Create a helper column "Total4s" that counts the number of 4s on each row. Use this formula (and convert it to a column formula):
=COUNTIF([Pre Trip Completed]@row:[Doors/Gates]@row, "4")
Then in your Summary fields, use SUMIFS to add up the total number of "4"s where the System field is ITCMW, ITCT, METC, etc.
=SUMIFS([Total4s]:[Total4s], [System]:[System], "ITCMW")
Repeat for "ITCT" and "METC".
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!