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

Options

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

  • Melissa Boehl
    Melissa Boehl ✭✭✭✭✭✭
    Answer ✓
    Options

    @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

    Smartsheet Architect | TurningPoint Energy

Answers

  • Melissa Boehl
    Melissa Boehl ✭✭✭✭✭✭
    Options

    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

  • A Rose
    A Rose ✭✭✭✭✭
    edited 08/10/22
    Options

    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
    Melissa Boehl ✭✭✭✭✭✭
    Answer ✓
    Options

    @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

    Smartsheet Architect | TurningPoint Energy

  • A Rose
    A Rose ✭✭✭✭✭
    Options

    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
    Melissa Boehl ✭✭✭✭✭✭
    Options

    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

  • A Rose
    A Rose ✭✭✭✭✭
    Options

    Got it, Thanks for sharing!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!