Why is this formula #invalid operation

=COUNTIFS(Flight:Flight, "FSC", [Date of Award]:[Date of Award], >1 / 1 / 2023, [Date of Award]:[Date of Award], <3 / 30 / 2023)


If I put the dates in parenthesizes it yeilds 0 even though I have "FSC" in the Flight column. If I take them away it becomes #invalid.


Ricky T

Gov Contractor for USSF

Tags:

Best Answer

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    Answer ✓

    Try it with the Date function:

    =COUNTIFS(Flight:Flight, "FSC", [Date of Award]:[Date of Award], > DATE(2023, 1, 1), [Date of Award]:[Date of Award], < DATE(2023, 3, 30)

Answers

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    Answer ✓

    Try it with the Date function:

    =COUNTIFS(Flight:Flight, "FSC", [Date of Award]:[Date of Award], > DATE(2023, 1, 1), [Date of Award]:[Date of Award], < DATE(2023, 3, 30)

  • juliesilverio
    juliesilverio ✭✭✭✭

    Hi Ricky,

    I believe it is because of your date formatting. I would try the below:

    =COUNTIFS([Flight]:[Flight] "FSC", [Date of Award]:[Date of Award], AND(@cell >= DATE(2023, 01, 01), @cell <= DATE(2023, 03, 31)))

    Hope that helps!

    Julie Silverio, PMP

    Senior Manager, Program Leadership

    Xencor Inc.

    https://xencor.com/

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!