CODEWITHSUNDEEP
if-statementgoogle-sheetsgoogle-sheets-formulacountingcountif
Rebecca Sacks
Rebecca Sacks

Reputation: 21

Counting nth occurrence of a value

Hoping I can be clear about this. I'm designing a digital lost & found system for a school I work for. Parents will fill out a google form which feeds to a spreadsheet, and I want to be able to track 1st, 2nd, 3rd, etc. requests for any given item (numbered 1-58).

Essentially, I want the sheet to look like:

Name Grade Item Request No.
Sally 1st 51 1
Joey 2nd 3 1
Suzy 2nd 51 2
Sally 1st 27 1
Amahl 3rd 51 3

And so on...I imagine this can be done using vlookup, but I'm drawing a blank as to how...

Upvotes: 1

Views: 791

Answers (2)

player0
player0

Reputation: 1

use:

=INDEX(IF(C2:C="";;COUNTIFS(C2:C; C2:C; ROW(C2:C); "<="&ROW(C2:C))))

enter image description here

Upvotes: 1

Chris Strickland
Chris Strickland

Reputation: 3490

Try a dynamically expanding range:

=countifs($C$2:C2, C2)

Where the values you're counting are in C:C and you make the first bound of the range static ($C$2) and the second dynamic (C2). Then when you fill down, the range will grow.

See this demo: https://docs.google.com/spreadsheets/d/1-AsjQ3sqTIpDOpEMSGLsullLY9P-0bEpuzkFAiQXwV0/edit#gid=0

I have assumed that the only criterion is the issue number and the grade, student, etc, don't matter.

Let me know if that works.

Upvotes: 2

Related Questions