How to use @row inside the OR function

Paul G.

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.

Kelly Moore

Hey @Paul G.

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

Does this work for you

Kelly

Kelly Moore

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

Kelly Moore

Hey @Paul G.

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

Does this work for you

Kelly

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

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.

@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