You can do this using array formulas. In your cell
C2, write the following:
then use Ctrl+Shift+Enter to register it as an array formula. Copy it to the rest of the column afterward.
IF function is the ordinary sort: if in the B column we see “apple”, we do stuff, otherwise just write the empty string. The interesting part is the second argument — that which gives us the result in the “apple” case.
Now first consider what Excel would do with a simpler formula:
SUM(A:A=A2). Inside the
SUM, Excel sees an equality of the form “range = value”. If both sides were simple values, that would evaluate to a boolean, but here because you used Ctrl+Shift+Enter to turn on array formulas, Excel applies the operation to each element on the left side individually, and stores them in memory in a temporary array, which
SUM happily accepts. The result is thus the number of cells in column
A that are equal to
Well, almost. This would be the case if
TRUE values as 1 and
FALSE values as 0. That not being the case, we would need to convert the booleans into numbers, one way being a double negation like so:
* operator in the actual formula will take care of that too though.
Back to the actual sum, we have the additional term
ISNUMBER(A:A). The principle is similar: this will again operate elementwise on column
A as it doesn't ordinarily know what to do with a range. Then the
* operator “multiplies” the two temporary ranges containing booleans elementwise — basically applying the
AND operation to them — and giving us a new range of booleans. (This is here just to ensure that blank cells are not counted as equal to 0, and
NOT(ISBLANK(A:A)) would likewise work.)
Finally we do the same with the additional range of booleans that describe whether each individual value in column
B is not equal to
B2. In the end,
SUM then acts on the resulting range.
In conclusion, we counted rows with numbers in the first column, where the current row is match in column
A, but not in column
B. Your condition just says that the number of these is positive.
We use the
* operator instead of the
AND function because the latter happens to accept ranges as argument by default, so it would just eat up the temporary ranges instead of activating the “array mode” and performing an elementwise operation.
You can freely make the columns absolute for copying the formula to other columns, add (absolute) row numbers to the ranges
B:B to ignore other stuff potentially located in the same column (as well as performance) — or even make named ranges of the two columns like
Fruits, then put these names in the formula instead of the ranges.