0

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

Improve this question
1
  • 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.
    – Rubén ♦
    May 5 at 3:41

2 Answers 2

Reset to default
1

Solution was provided at the Google Sheets community.

=map( 
  B5:B, C5:C, 
  lambda( 
    name, shift, 
    countifs(B5:name, name, C5:shift, shift) 
  ) 
)
Improve this answer
1
0

Here's another solution:

=ARRAYFORMULA(COUNTIFS(B5:B,B5:B,C5:C,C5:C,ROW(B5:B),"<="&ROW(B5:B)))
Improve this answer
1
  • 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.
    – doubleunary
    May 5 at 7:34

Not the answer you're looking for? Browse other questions tagged or ask your own question.