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!
Best Answer
-
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
Smartsheet Architect | TurningPoint Energy
Answers
-
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!
Melissa Boehl
Smartsheet Architect | TurningPoint Energy
-
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
-
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
Smartsheet Architect | TurningPoint Energy
-
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?
-
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.
Melissa Boehl
Smartsheet Architect | TurningPoint Energy
-
Got it, Thanks for sharing!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!