Exceljet

Quick, clean, and to the point

Customer is new

Excel formula: Customer is new
Generic formula 
=(COUNTIFS($A$1:A1,A1)=1)+0
Explanation 

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.

How this formula works

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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course
Video training for Excel Tables