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

A Rose

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!

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

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

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

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!