# How do I generate a sequential number within an IF formula?

Columns A, B and C contain values which describe a product. I want column D to generate a sequential number based on the contents of a given cell in column A. Together the four values make up the product code.

• Column A: Location
• Column B: Species
• Column C: Date
• Column D: Reference Number

There are multiple locations which will fill the cells in column A. I want the reference number to begin with 1 for each location. My approach is to use a nested IF formula to isolate the locations, but I can't figure out how to generate a unique sequential reference number. Any thoughts?

-
Could you elaborate on how should the reference number (column D) be generated? just sequenced number, or should it depend columns A,B,C? – Michael Oct 15 '12 at 23:25
An example would be useful, too. – Scott Oct 15 '12 at 23:31

Slightly guessing what is required, if you sort your three columns A/B/C with columns labels in `Row1` then this formula in `D2` (or something similar) and copied down may suit: `=IF(AND(A2=A1,OR(B2<>B1,C2<>C1)),D1+1,1)`. The `OR` ensures that Species and Date are not both duplicates, the `AND` requires that the Location is a duplicate of the record immediately above. `IF` increments the counter if so and resets to `1` if not.