When using a ...IF type of function (like COUNTIF, AVERAGEIF, etc.), I use condition strings:

  • e.g. ">100", "=Mr. Smith"

Instead of using a literal from within the string, how do I (or can I) reference a different cell from within the table?

  • e.g. ">ColA Row2"

Stuff I've Tried (that doesn't work)

  • 1
    Have you tried just using the cell reference directly (A2, for example)? That works in Excel and it should be the same in Numbers. – fsb Nov 6 '18 at 18:01
  • Yes, I've tried that. Please see edits to question. – Anthony C Nov 8 '18 at 15:48
  • That doesn't look right. The use is COUNTIF(Range,Criteria). It should be similar to =COUNTIF(A2:A10, "Mr. Smith"). – fsb Nov 8 '18 at 16:00
  • @fsb - I don't know why you think it doesn't look right — that usage is correct, I have spreadsheets using it right now. This is copied directly from the documentation: =COUNTIF(A1:D5, “=ipsum”) returns 1, because the text string “ipsum” appears once in the collection referenced by the range. – Anthony C Nov 9 '18 at 22:06
  • It didn't look right because of how you're displaying it in your question: "...,"=A2",... ...,=A2,...". The way it's written in the documentation is the way I use it so it does work. Have you tried isolating the COUNTIF to a different cell and see if that throws an error? Maybe it's a different part of your formula that's generating it? – fsb Nov 9 '18 at 22:18

When directly referencing a cell from within an IF type function, don't use a condition string. Also, no need to use a comparison operator.

  • Condition string usage, to compare against a literal value:

    COUNTIF(A1:A20, "=Mr. Smith")

  • Direct cell reference, to compare against cell B1:

    COUNTIF(A1:A20, B1)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .