How to use @row inside the OR function

When using the OR function inside COUNTIF, can @row be used? The following is a basic example when calculating the number of times an agency names appear.

·      The formula references the Agency column in another sheet which has three possibilities. One option is “BOTH” which represents both agencies. Therefore, a count of one would be calculated for each of the two agencies.

·      On the formula/calculation sheet:

·      The name of the two agencies appear in row 1 and row 2 in the Agency column.

·      The next column (formula column) is to the right and contains the formula.

How can the formula be written to count for agency name using @row and “BOTH”?

I have tried =COUNTIF({Agency}, OR(Agency@row, @cell = "Both")) but it causes an Invalid Data Type error.

Thank you for your response.

Best Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Paul G.

    =COUNTIF({Agency}, OR(@cell=Agency@row, @cell = "Both"))

    Does this work for you

    Kelly

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey Paul

    Good question - if I misspeak I'm hoping other community members will jump in and help out.

    Let me answer the easier question first - no, I don't use IFERROR every time I use @cell. If I am working with a Date function, because Date functions are easy to get errors with, I might use an IFERROR. I do this because of the function, not necessarily because I used @cell. I hope that makes sense. Notice the example in the article is the YEAR function - which is a Date function.

    So when do I use @cell? - when I'm doing some sort of calculation or evaluation within a range, and I need that evaluation to go down the rows one by one. For example, suppose you wanted to COUNT when cells in Column A contained the word Dog. = COUNTIFS([Column A]:[Column A], CONTAINS("Dog", @cell). The @cell is the only way to designate the range within the CONTAINS function.

    There are some instances, like the CONTAINS function above, where you must use @cell. In other formulas, you can try the formula without it and if the formula works, great! If it doesn't work, add the @cell and see if that works.

    I'm not sure I really answered your questions.

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Paul G.

    =COUNTIF({Agency}, OR(@cell=Agency@row, @cell = "Both"))

    Does this work for you

    Kelly

  • Paul G.
    Paul G. ✭✭✭✭✭

    Good morning @Kelly Moore .

    Thank you for your answer. And yes it does work, no error messages.

    I am still learning Smartsheet and have read the article on Create Efficient Formulas with @cell but I am still uncertain under what circumstances you use @cell as it is not clear in the article. It references when wanting to perform calculations in formulas looking at ranges. So does this mean every time you have a range of cells one should use @cell?

    For example in the COUNTIF with OR formula you assisted with if I was going to write it as a COUNTIF without the OR should it always be written =COUNTIF(SearchRange,@cell="Whatever your searching for")?

    Also, the article mentions wrapping inside of IFERROR incase there are any blank cells. Is that the standard to use IFERROR whenever using @cells?

    I understand IFERROR from a number of years working in Excel. The @cell is a new concept that I am needing to understand and learn and the article does not provide clear information on the specifics of when to use it.

    Thanks.

    Paul

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey Paul

    Good question - if I misspeak I'm hoping other community members will jump in and help out.

    Let me answer the easier question first - no, I don't use IFERROR every time I use @cell. If I am working with a Date function, because Date functions are easy to get errors with, I might use an IFERROR. I do this because of the function, not necessarily because I used @cell. I hope that makes sense. Notice the example in the article is the YEAR function - which is a Date function.

    So when do I use @cell? - when I'm doing some sort of calculation or evaluation within a range, and I need that evaluation to go down the rows one by one. For example, suppose you wanted to COUNT when cells in Column A contained the word Dog. = COUNTIFS([Column A]:[Column A], CONTAINS("Dog", @cell). The @cell is the only way to designate the range within the CONTAINS function.

    There are some instances, like the CONTAINS function above, where you must use @cell. In other formulas, you can try the formula without it and if the formula works, great! If it doesn't work, add the @cell and see if that works.

    I'm not sure I really answered your questions.

    Kelly

  • Paul G.
    Paul G. ✭✭✭✭✭

    @Kelly Moore Thank you. Your information is helpful. It provides insight into the use of @cell.

    I appreciate your time to help provide a solution and explanation.

    Paul

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!