Help with Countifs formula
I am trying to count the number of "failures" within a certain range of columns (about 67 columns) that also are specific to a cabin name "Agnis" (for example). I keep getting an incorrect argument or error on the formula.
This is the other sheet I am referencing:
The range of "failures" is columns starting at "Driveway sign rating" and extends over about 60+ more columns so I am trying to capture all those columns. Then, I want to only count them if the Cabin name is specific. For this example, it would be in the "Cabin Name" column.
I am having trouble with the ranges I select and it returns an error. Note that this example is a form entry sheet.
Help is appreciated! Thanks.
Answers
-
You are going to need a helper column on your source sheet with a COUNTIFS to output the number of Failures on each row. Then you would SUM this helper column on your metrics sheet.
If you want to also do this for the other ratings, you would need to add a helper column for each rating.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
The only way I know to do it is to add multiple countifs formulas. The range of more than one column doesn't seem to work with the countifs formula. It works with countif but not countifs.
Example
=COUNTIFS({Driveway Sign Rating},"Failure",{Cabin Name},"Agnis")+COUNTIFS({Driveway/Parking Spot Rating},"Failure",{Cabin Name},"Agnis") continue adding for each of your columns
-
@Hollie Green The reason the multi-column range isn't working is because all ranges within a function MUST be of the same shape/size. In the COUNTIF you only have one range. In the COUNTIFS you have two ranges. One is multiple columns and the other is single columns. If both were single columns or both were multiple columns (and the same number of columns) then it would work.
Generally speaking I do usually go with the COUNTIFS + COUNTIFS solution, but in this case that means stacking 67 individual COUNTIFS in there. That's why I went with the helper columns on the source sheet to get the totals for each score. 5 COUNTIFS is a lot easier to manage than 67 COUNTIFS.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
I'm back again with another modification to this formula. How would I update this to only count if the "cabin type" is a 1B or 2B etc. @Paul Newcome
I have this formula (which is in my helper columns)
=COUNTIFS([Driveway / Parking Spot Rating]2:[Entry Lock Type]2, OR(@cell = "", @cell <> ""))
but I want it to only count that range if the cabin type column is a specific identifier (ie 1B). This is because it is skewing the number of cells that it is adding, because some cells are only related to a certain type of cabin.
-
You would use an IF statement like so:
=IF([Cabin Type]@row = "1B", COUNTIFS([Driveway / Parking Spot Rating]2:[Entry Lock Type]2, OR(@cell = "", @cell <> "")))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!