CountIF and OR Formula

Options

Hello,

I'm trying to construct a COUNTIFS formula which is currently:

=COUNTIFS({Arrival Date}, {Arrival Date} <= DATE(2023, 7, 31), {Arrival Date}, >=DATE(2023, 7, 1), {Status}, "Provisional")

This works and returns a value but I also want it to include {Status} of "Confirmed" as well as "Provisional". I've tried:

=COUNTIFS({Arrival Date}, {Arrival Date} <= DATE(2023, 7, 31), {Arrival Date}, >=DATE(2023, 7, 1), OR({Status}, "Provisional", "Confirmed"))

and variations abut neither work. I'm just trying to include "Confirmed" in the criterion so it adds both provisional and confirmed from the status column into the formula.

Thanks in advance for your help.

Tags:

Answers

  • Frank S.
    Frank S. ✭✭✭✭✭✭
    Options

    Greetings @justdan2,

    Here is a modification of your formula that should resolve your issue:

    =COUNTIFS({Arrival Date}, {Arrival Date} <= DATE(2023, 7, 31), {Arrival Date}, >=DATE(2023, 7, 1), OR({Status}, @cell = "Confirmed", @cell = "Provisional"))

    Let me know if that helps.

    Frank Smith, PMP

    Assistant Director | IT Special Projects Mgr.

    Oregon Parks & Recreation Department

    If my response helps, please mark it as an accepted answer. 😎

  • justdan2
    justdan2 ✭✭✭✭
    Options

    Hi Frank,

    Thanks for your assistance here.

    Do I have to reference something using the '@cell'. When I add it in as you have typed above it returns 'Invalid Data Type'.

    Thanks.

  • Frank S.
    Frank S. ✭✭✭✭✭✭
    Options

    @justdan2,

    Here is an update:

    =COUNTIFS({Arrival Date}, {Arrival Date} <= DATE(2023, 7, 31), {Arrival Date}, >=DATE(2023, 7, 1), {Status}, OR(@cell = "Confirmed", @cell = "Provisional"))

    The {Status} could either come directly from the sheet or another sheet.

    I inadvertently placed it inside the OR statement.

    Frank Smith, PMP

    Assistant Director | IT Special Projects Mgr.

    Oregon Parks & Recreation Department

    If my response helps, please mark it as an accepted answer. 😎

  • justdan2
    justdan2 ✭✭✭✭
    Options

    Perfect, that works. Thanks so much for your help.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!