**Quick answer:**

You can do this using array formulas. In your cell `C2`

, write the following:

```
=IF(B2="apple",SUM(ISNUMBER(A:A)*(A:A=A2)*(B:B<>B2))>0,"")
```

then use Ctrl+Shift+Enter to register it as an array formula. Copy it to the rest of the column afterward.

**Explanation:**

The `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 `A2`

...

Well, almost. This would be the case if `SUM`

treated `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: `SUM(--(A:A=A2))`

. The `*`

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.

*Remark 1:*

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.

*Remark 2:*

You can freely make the columns absolute for copying the formula to other columns, add (absolute) row numbers to the ranges `A:A`

and `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 `IDs`

and `Fruits`

, then put these names in the formula instead of the ranges.