I'd like to Calculate date ranges for a specific type of project

Options

I am getting different errors for the formula below, I think it is some comma or position I am missing.


=COUNTIFS({EOTS}, "1",{CDate}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - 1)

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @gwen.pino78261

    What errors are you getting?

    The structure of the formula looks alright! However depending on what your {EOTS} column is, you may want to try the 1 without quotes. Quotes turns a number into text. If you're searching for 100% or 1, try this:

    =COUNTIFS({EOTS}, 1,{CDate}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - 1)

    If this doesn't work, try each part of the formula individually:

    =COUNTIFS({EOTS}, 1)

    =COUNTIFS({CDate}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - 1)


    I would also suggest checking each of the ranges {EOTS} and {CDate} to ensure they're looking at the right column (click on one then choose Edit Reference to see the column).

    Let me know if any of this has helped!

    Cheers,

    Genevieve

  • gwen.pino78261
    gwen.pino78261 ✭✭✭✭
    Options

    What was interesting...sometimes putting the {EOTS},"1" at the end of a Countifs statement worked and other times it had to be the first constraint.


    Thanks

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @gwen.pino78261

    The order of column and criteria within the formula shouldn't make a difference, similar to adding criteria to a filter.

    Is it working for you now? If not, it would be helpful to know what errors you're getting and to see a screen capture of the sheet you're referencing, but please block out sensitive data.

    Cheers,

    Genevieve

  • gwen.pino78261
    gwen.pino78261 ✭✭✭✭
    Options

    it is working for me now...

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!