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.

Thanks in advance for your help.

Find more posts tagged with

Formulas

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.