# CountIF and OR Formula

✭✭✭✭

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.

Tags:

• ✭✭✭✭✭✭

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. 😎

• ✭✭✭✭

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.

• ✭✭✭✭✭✭

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. 😎

• ✭✭✭✭

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!