How to use OR for COUNTIFS, referencing 2 columns in another sheet

Hi,

I have the following formula in a column,

=COUNTIFS({Sheet Range 1}, [Column Sample]@row, {Sheet Range 2}, "ABC", OR({Sheet Range 3}, >TODAY(), {Sheet Range 4}, >TODAY()

I need to do if column A = 1. column B = 2, (next should be OR) or column C = 3 or column D = 4

Please help out!

Find more posts tagged with

Formulas

functionality

Accepted answers

Melissa Boehl

@A Rose

If I were trying to complete this task in my sheets, I would just add a checkbox helper column to Sheet 2. It can easily be hidden from view after you've created your formula. This is how I would do it: On sheet 2, create a checkbox column and name it count or whatever you think works best. Create a column formula referencing the 2 dates. This is what I used: =IF(OR([Date 1]@row > TODAY(), [Date 2]@row > TODAY()), 1, 0). This will check the box if either date is in the future. Then go to sheet 1 and enter the following formula in your formula column: =COUNTIFS({contact}, Contacts@row, {column 3}, "abc", {count}, 1)

Here are a few screenshots. I hope this helps you accomplish your goal. If this solves your problem, please mark it as answered. Thanks!

Sheet 2

Sheet 1

All comments

Melissa Boehl

Could you share some screenshots or make a copy of the sheet and remove sensitive data and share with me? I will be happy to help but it is hard to figure it out without a visual reference. Thanks!

A. Rose

Hi,

See 2 screenshots below, it says countifs reference = contact@row, Column 3 = "ABC" and reference date 1 or reference date 2 is greater than today, I'm just unsure how to use the or in this formula.

Thanks for your help! @Melissa Boehl

Melissa Boehl

@A Rose

Here are a few screenshots. I hope this helps you accomplish your goal. If this solves your problem, please mark it as answered. Thanks!

Sheet 2

Sheet 1

A. Rose

Hi @Melissa Boehl,

That works! thanks!

I'm using it on a sheet with a lot of columns, I wouldn't want to add another column only if I have to,

Don't you think it's doable without the helper column, with a better formula?

Melissa Boehl

Honestly I had run into this same problem a while back and no one could offer me a better solution. It is possible that someone may have a different workaround but there isn't one that I know of. If I hear of one, I will absolutely reach back out to you and let you know.

A. Rose

Got it, Thanks for sharing!

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.