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

gwen.pino78261

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)

Genevieve P.

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

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.

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

it is working for me now...