Stuck on a COUNTIF formula to use

Hi all

I'm still trying to get my head around Smartsheet formulas with mixed success, many times i try to use the AI formula creator but it often fails for me…

Im trying to create a formula that addresses the below AI query that keeps either returning an #Unparseable error or #Invalid Data Type.

Basically, in my sheet i want to get a "summary" of all the Opportunities for Improvement logged in 2025 where the closed date is less than the Agreed closure date. Meaning i am within my KPI, if its beyond the agreed closure date, I am missing my KPI. But i get the errors as stated.

I also have a "Working Days Overdue" column that is calculated and working, can i use this column somehow? ie, if days overdues is a minus figure (i am good) if its a positive figure (its not good)

Can someone help me get the formula working please? Any help greatly appreciated.

Thanks

Matt

Find more posts tagged with

Accepted answers

Paul Newcome

You will definitely need to leverage the [Working Days Overdue] column.

=COUNTIFS(Finding:Finding, @cell = "Opportunity For Improvement", [Date Logged]:[Date Logged], IFERROR(YEAR(@cell), 0) = 2025, [Working Days Overdue]:[Working Days Overdue], @cell > 0)

Paul Newcome

Is that error present in any cell within any of the referenced ranges? Possibly in the [Working Days Overdue] column?

All comments

Paul Newcome

You will definitely need to leverage the [Working Days Overdue] column.

=COUNTIFS(Finding:Finding, @cell = "Opportunity For Improvement", [Date Logged]:[Date Logged], IFERROR(YEAR(@cell), 0) = 2025, [Working Days Overdue]:[Working Days Overdue], @cell > 0)

MattB_ADS

Hi Paul

Thanks for your reply, but when I drop your formula into a text/number Summary sheet field i still get "#Invalid Data Type". Have I got something wrong here?

Thanks again

Paul Newcome

Is that error present in any cell within any of the referenced ranges? Possibly in the [Working Days Overdue] column?

MattB_ADS

Hi Paul, yes you were right i had some items that did not have the correct Working Days overdue figure causing that error. Thanks again for your help to get resolved.

Paul Newcome

Happy to help. 👍️

MattB_ADS

Hi Paul, can i ask something else related to this query please?

Im Basically, trying to find audit items completed on time that were created in 2025 (this is a text field that had options 2025/2026/2027. based on our internal audit year. I'm looking for items that have a status of "Completed" or "Closed after Acceptance" so the 2 statements are joined with a + .

=COUNTIFS( {New ISO27001 Internal Audit Schedule Range 4}, HAS(@cell, "2025"), {New ISO27001 Internal Audit Schedule Range 5}, "Complete", {WDO Numeric Range}, "<0")+COUNTIFS( {New ISO27001 Internal Audit Schedule Range 4}, HAS(@cell, "2025"), {New ISO27001 Internal Audit Schedule Range 5}, "Closed after Acceptance", {WDO Numeric Range}, "<0")

but i get an error #INCORRECT ARGUMENT SET

I threw it into CoPilot and it says i need a Helper column…

Now i have a "Working Days Overdue helper" column that is using this formula:

=IFERROR(VALUE([DaysOver]@row), 0)

If i reference that helpder colum in my overall formula, i still get the error. Is there a better way for me to get my formula working with what i need in the 2nd paragraph above?

Many thanks for any help

Matt

Paul Newcome

You don't need a helper column. Just wrap whatever formula you have int ehh [Working Days Overdue] column in an IFERROR to output a blank in the event of an error.

=IFERROR(existing working days formula, "")

Then try this for your COUNTIFS:

=COUNTIFS({Year Column Range}, @cell = 2025, {Status Column Range}, OR(@cell = "Complete", @cell = "Closed After Acceptance"), {Working Days Column}, @cell > 0)

NOTE: Use caution when trying to work with AI for Smartsheet formulas and whatnot. Even the AI that is built in to Smartsheet gets it wrong sometimes.

MattB_ADS

Hi Paul

Could you help me with that IFERROR line pls

I have this in the Workding Days Overdue formula

=NETWORKDAYS([Date scheduled]@row, [Date Audited]@row)

Also, without the IFERROR formula, the COUNTIFS formula is still erroring you provided.

Many thanks

Matt

Paul Newcome

An error in a referenced cell will pull through into any formula referencing that cell. We have to remove all errors from the referenced data first.

=IFERROR(NETWORKDAYS([Date scheduled]@row, [Date Audited]@row), 0)

MattB_ADS

Thanks Paul

OK, so all line items under WDO now have a value (zero if there is no calcuation of overdue date etc) i have added a column formula of

=IFERROR(NETWORKDAYS([Date scheduled]@row, [Date Audited]@row), 0)

but i still get the error #INCORRECT ARGUMENT SET

Paul Newcome

Ok. The next step for that error is to double check all of your {Cross Sheet References} are pulling from the same sheet and are all covering the entire appropriate column by selecting the column header.

Give the window a minute to load before selecting the column header for the {Range}. Sometimes it will allow you to make a selection before it has completely loaded. Once it does finally completely load, the range selection reverts back to the top left cell in the reference sheet, and it does not let you know that this has happened.

If that doesn't work, a screenshot of the COUNTIFS open in the sheet as if you are editing it may be useful.

MattB_ADS

Hi Paul, i think i can see a problem with the references being used, i'll have a look , double check them all and come back to you soon. Thanks very much for your help again, really appreciated! :-)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.