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
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!