Adding another requirement for Countifs formula

Options

I know this should be easy but I cannot get a good return. The current formula I use is as follows:

=COUNTIFS({Initial Date - POR}, IFERROR(YEAR(@cell), 0) = 2020, {ROT - POR}, 1)


Works fine except I want to add another criteria from the same sheet:

(Status, "Closed")


And since this is a sheet with multiple years, it just counts everything and not just the year when I do get a return.


Help appreciated!

Best Answer

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓
    Options

    @beth_linder28141

    You would need to add another cross sheet reference to pull in the status column. Your formula would then be something like this:


    =COUNTIFS({Initial Date - POR}, IFERROR(YEAR(@cell), 0) = 2020, {ROT - POR}, 1,{status range},"Closed")

Answers

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓
    Options

    @beth_linder28141

    You would need to add another cross sheet reference to pull in the status column. Your formula would then be something like this:


    =COUNTIFS({Initial Date - POR}, IFERROR(YEAR(@cell), 0) = 2020, {ROT - POR}, 1,{status range},"Closed")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!