# Automatic Numerical Incrementation [closed]

I'm trying to get automatic incrementation working for a dynamic range of data. I've got this convoluted formula that works in some of the cells but not others, returning a #Value error that doesn't make sense to me since it only occurs after a certain point. If there is a less complicated way to solve this, I'm absolutely open to it as well.

Columns B & C contain dropdown options for scheduling purposes. What I'm looking for column D to do is to auto increment the number based on whether that exact combination of names/shift has previously occurred. Sample sheet is here: https://docs.google.com/spreadsheets/d/1LMZgTRmh6cH0a2o0UBNHXPfHhlpFOg-2yEzAO7Us78s/edit#gid=0

• Welcome to Web Applications. While a link to an external spreadsheet might be helpful, questions on this site should be self-contained. Please describe your spreadsheet structure and the relevant settings, i.e., the spreadsheet locale, as it determines the decimal separator and the function parameter separator, among other things. Include sample input data and the expected results as text. Commented May 5, 2023 at 3:41

Solution was provided at the Google Sheets community.

``````=map(
B5:B, C5:C,
lambda(
name, shift,
countifs(B5:name, name, C5:shift, shift)
)
)
``````

Here's another solution:

``````=ARRAYFORMULA(COUNTIFS(B5:B,B5:B,C5:C,C5:C,ROW(B5:B),"<="&ROW(B5:B)))
``````
• I think this is the fastest "trivial" way of getting a cumulative category count. To avoid filling the bottom of the column with zeros, wrap as is `=arrayformula( iferror( 1 / countifs(B2:B, B2:B, C2:C, C2:C, row(B2:B), "<=" & row(B2:B)) ^ -1 ) )`. With larger datasets, you will get better performance with one of these solutions. Commented May 5, 2023 at 7:34