COUNTIFS Formula "NOT" error
I want to calculate something for a dashboard. My formula references our team's tracker sheet. I am looking to use COUNTIFS for Tasks that are a specific Request Type, not a grand parent row, and not in our Archive section. The first part of my formula works, my Archive NOT rule is what gives me an error. I've included a screenshot with my helper row unhidden. The helper row lets me single out the ancestors marked "3." Those rows will be our task names for reporting and hides their children.
=COUNTIFS({Operations Training Team Tracker Range 3}, FIND("e-Learning Module", @cell) > 0, {Operations Training Team Tracker Range 2}, >2, {Operations Training Team Tracker Range 4}, NOT(@cell = "Archive"))
Best Answer
-
Start by double checking your ranges. I know that sometimes when creating cross sheet references, I will select the range a little too quickly before the sheet has had a chance to completely load. When the sheet finishes completely loading, the range reverts back to the home cell (top left corner) of the sheet and I have to go back and re-select the correct range.
I also notice that your numbers in your helper column are left justified. If you have not applied any formatting to that column, this means they are actually being stored as text instead of numerical values. This shouldn't throw the #INCORRECT ARGUMENT SET error, but it could be an issue once we get that particular bit fixed.
Another thing to check would be the source sheet. Look in the ranges themselves. Is that error present in any cell that is being looked at by one of your cross sheet references?
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!
Answers
-
What column(s) are included in each of your ranges, and what error are you getting?
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!
-
{Master Project List - Project Health} = "Request Type" column
{Operations Training Team Tracker Range 2} = "Helper" column
{Operations Training Team Tracker Range 4} = "Status" column
I get #INCORRECT ARGUMENT SET
-
Start by double checking your ranges. I know that sometimes when creating cross sheet references, I will select the range a little too quickly before the sheet has had a chance to completely load. When the sheet finishes completely loading, the range reverts back to the home cell (top left corner) of the sheet and I have to go back and re-select the correct range.
I also notice that your numbers in your helper column are left justified. If you have not applied any formatting to that column, this means they are actually being stored as text instead of numerical values. This shouldn't throw the #INCORRECT ARGUMENT SET error, but it could be an issue once we get that particular bit fixed.
Another thing to check would be the source sheet. Look in the ranges themselves. Is that error present in any cell that is being looked at by one of your cross sheet references?
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!
-
Funny enough I think you were right, after I cleared the left align formatting on my helper column and refreshed the page all worked fine! Great idea, would have never thought that was the issue, thanks :)
-
@Alysse Griffith I am glad to hear everything is working! 👍️
It shouldn't have had to do with any "custom" formatting though. If you had intentionally set the column to be left aligned, then resetting it should not have made a difference.
What I was referring to is if there is NO custom formatting applied to the column numerical values will be right aligned and text values left aligned. Custom formatting options such as the side alignment should not have affected the data itself, only how it was displayed.
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.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!