Customer is new

To mark a customer as new in a list or table, you can use the COUNTIFS function and an expanding range in a helper column. In the example shown, the formula in E5, copied down, is:

=(COUNTIFS($B$5:B5,B5)=1)+0

The first time a customer appears in the list, the formula returns 1. Subsequent occurrences return zero.

Explanation

This formula uses an expanding range for the criteria range inside COUNTIFS:

COUNTIFS($B$5:B5,B5)

Because the first reference is absolute and the second reference is relative, the range expands as the formula is copied down the column. The criteria is simply the value in the current row of column B.

COUNTIFS returns the count of the current customer up to that point in the data. This means the first occurrence of a customer is 1, the second is 2, and so on. Because we only care about the first occurrence, we compare the count to 1:

COUNTIFS($B$5:B5,B5)=1

This expression will return TRUE when the count is 1 and FALSE for any other value.

Finally, to force a 1 or 0 result, we add zero. The math operation causes Excel to coerce TRUE and FALSE to equivalent numbers, 1 and 0.

Note: The example above uses first name for customer id. This is not realistic, but it makes it easy for the human eye to track. In normal data, customer id will be a unique number of some kind.