How do I reference a cell from within a "...IF"-type function condition string?

Question

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

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

...,"=A2",...
...,=A2,...

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 06 '18 at 18:01
That doesn't look right. The use is COUNTIF(Range,Criteria). It should be similar to =COUNTIF(A2:A10, "Mr. Smith"). — fsb, Nov 08 '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 09 '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 09 '18 at 22:18
Okay, I've figured out what I was doing wrong. My wrong attempts: COUNTIF(A2:A10, "=A2"), COUNTIF(A2:A10, =A2). The correct answer: COUNTIF(A2:A10, A2). I'll accept your answer if you enter one. Thank you. — Anthony C, Nov 10 '18 at 23:20

score 0 · Accepted Answer · answered Dec 10 '18 at 23:56

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)

1 Answers1