Inconsistent Formula Issue #INCORRECT ARGUMENT SET error

Dylan G

Hello,

I am working in a sheet summary and I am using a formula to count the number and type of maintenance issues after a certain date.

My issue is that I am using the exact same formula in different fields in the sheet summary and only changing the text in a countifs formula. It works in some cases but not others.

For example, the following formula works great: =COUNTIFS(Category:Category, "NIBRS/Validation", [Completed **Notifies Agency**]:[Completed **Notifies Agency**], 1, [Date Resolved]:[Date Resolved], >=(DATE([Go Live Date in Formula Format]#)))

This formula throws an #INCORRECT ARGUMENT SET error: =COUNTIFS(Category:Category, "User Knowledge/Training", [Completed **Notifies Agency**]:[Completed **Notifies Agency**], 1, [Date Resolved]:[Date Resolved], >=(DATE([Go Live Date in Formula Format]#)))

The date near the end seems to be the issue, but it is the same in both formulas. I am referencing another field in the sheet summary for the date, and when I enter the date manually it resolves my error.

The date field I am referencing has the date 2024,03,12, and it is created referencing another cell where the date is in the format 03/12/24 and I have used the formula ="20" + RIGHT([Go Live Estimate]#, 2) + "," + LEFT([Go Live Estimate]#, 2) + "," + MID([Go Live Estimate]#, 4, 2) to convert the date into the format YYYY,MM,DD.

I'm stumped and appreciate any help anyone can give.

Thank you

Paul Newcome

This formula does not create a valid date value:

="20" + RIGHT([Go Live Estimate]#, 2) + "," + LEFT([Go Live Estimate]#, 2) + "," + MID([Go Live Estimate]#, 4, 2)

It only creates a text string. To get a valid date value, you have to use the DATE function.

=DATE(VALUE("20" + RIGHT([Go Live Estimate]#, 2)), VALUE(LEFT([Go Live Estimate]#, 2)), VALUE(MID([Go Live Estimate]#, 4, 2)))

Dylan G

Thanks Paul. The formula you pasted is giving me an #INVALID COLUMN NAME error. It appears to be referencing the correct cell in the sheet summary

Paul Newcome

"Invalid Column NAME" or "Invalid Column VALUE"? Make sure the sheet summary field you have the formula in is set as a date type field.

Dylan G

Definitely invalid column name. The sheet summary field is in a date format

Paul Newcome

The INVALID COLUMN VALUE error comes from outputting the wrong data type. Can you provide a screenshot of the summary field properties?

Dylan G

Screenshot 2024-02-19 112137.png

Screenshot 2024-02-19 112202.png

Yes sir. Here they are. If I change the "Go Live Date in Formula Format" field to a date it formats it in MM/DD/YY format

Paul Newcome

Right. But it needs to be a date value for the other formulas to be able to use it.

Dylan G

My understanding was that for formulas to use a date they needed to be in YYYY,MM,DD format. Is that not the case?

Paul Newcome

No. The DATE function requires yyyy, mm, dd format within the function, but each of those variables need to be numbers.

This formula outputs a text string:

="20" + RIGHT([Go Live Estimate]#, 2) + "," + LEFT([Go Live Estimate]#, 2) + "," + MID([Go Live Estimate]#, 4, 2)

Nothing is stored as a number.

If you need to reference your [Go Live Estimate], and it is already being stored as a date value, you can just reference the [Go Live Estimate]# field.

=COUNTIFS(Category:Category, "User Knowledge/Training", [Completed **Notifies Agency**]:[Completed **Notifies Agency**], 1, [Date Resolved]:[Date Resolved], >=[Go Live Estimate]#)

Dylan G

Thanks Paul. Thank is mostly working for me. I still have one formula in the sheet summary that appears to me to be the same as others, but is giving an #INCORRECT ARGUMENT SET error

This formula is working fine: =COUNTIFS(Priority:Priority, "1 - Critical", Status:Status, "Completed", [Date Resolved]:[Date Resolved], >=(DATE([Go Live Estimate]#)))

This formula is not working: =COUNTIFS(Priority:Priority, "2 - Major", Status:Status, "Completed", [Date Resolved]:[Date Resolved], >=(DATE([Go Live Estimate]#)))

Any idea why one would work and not the other? They are the same other than the criterion in the first COUNTIFS.

Paul Newcome

Try deleting that sheet summary field and retyping the formula from scratch into a new summary field.

Dylan G

A new field with the same formula still gives a #INCORRECT ARGUMENT SET error

Kelly Moore

Hey @Dylan G

as Paul’s formula illustrates, remove the DATE function in front of your [Go Live Estimate]# field

Paul Newcome

@Kelly Moore Thanks. I missed that it had been put back in there.

Dylan G

That did it! Thanks so much @Kelly Moore and @Paul Newcome