COUNTIFS Formula "NOT" error

Alysse Griffith · 01/28/20

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"))

Smartsheet Error.PNG

Paul Newcome · 01/29/20

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?

Paul Newcome · 01/28/20

What column(s) are included in each of your ranges, and what error are you getting?

Alysse Griffith · 01/28/20

{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

Paul Newcome · 01/29/20

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?

Alysse Griffith · 01/29/20

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 :)

Paul Newcome · 01/29/20

@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.

Formulas and Functions

Formulas and Functions

COUNTIFS Formula "NOT" error

Best Answer

Answers

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

When a specific value present, calculate date from Created to Date of completion

What formula do i need?

Nested If statements