CountIF and OR Formula

justdan2

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.

Frank S.

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.

justdan2

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.

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

justdan2

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