Countifs, Date Functions and Text Criteria

I am putting together two sets of formulas so I can capture the number of tasks within a certain year and month, and another set to see which of those are completed.

My formula that works to capture the number of tasks is (in this example, January 2024:

=COUNTIFS({GA Date}, IFERROR(MONTH(@cell), 0) = 1, {GA Date}, IFERROR(YEAR(@cell), 0) = 2024)

Now, I want to make a new formula to see if the Status is "Completed":

=COUNTIFS({GA Date}, IFERROR(MONTH(@cell), 0) = 1, {GA Date}, IFERROR(YEAR(@cell), 0) = 2024, {Status}, "Completed")

Now I am receiving an INCORRECT ARGUMENT SET error and not sure why. Both column ranges are the same, and I don't see any missing arguments or extra functions (as the documentation says).

Tags:

Best Answer

  • JamesB
    JamesB ✭✭✭✭✭✭
    edited 02/21/24 Answer ✓

    @James Tontarski I would move around you criterion, just to keep your nested formulas at the end. Then I would ask does your Status column on your other sheet have a formula that may be returning in error. If you have errored cells in your formula reference data, it will cause issues with your formula unless you have the IFERROR callout.

    =COUNTIFS({Status}, "Completed",{GA Date}, IFERROR(MONTH(@cell), 0) = 1, {GA Date}, IFERROR(YEAR(@cell), 0) = 2024)

    Edited to fix my formula mistake.

Answers

  • JamesB
    JamesB ✭✭✭✭✭✭
    edited 02/21/24 Answer ✓

    @James Tontarski I would move around you criterion, just to keep your nested formulas at the end. Then I would ask does your Status column on your other sheet have a formula that may be returning in error. If you have errored cells in your formula reference data, it will cause issues with your formula unless you have the IFERROR callout.

    =COUNTIFS({Status}, "Completed",{GA Date}, IFERROR(MONTH(@cell), 0) = 1, {GA Date}, IFERROR(YEAR(@cell), 0) = 2024)

    Edited to fix my formula mistake.

  • Thanks @JamesB, rearranging the criterion worked. The Status column in the other sheet has no formulas, just text.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!