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


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


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

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 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
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!