Question

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 · Accepted Answer

Hey @Paul G.

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

Does this work for you

Kelly

Kelly Moore · Accepted 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