CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Katherine
Katherine

Reputation: 1

Limit Number of Cells with the Same Value in Google Sheets

I'm looking to limit the number of cells that can have the same value in a column.

More precisely, I'm keeping track of people signed up for meeting dates and once four other people are assigned that date, it not to be an option anymore or highlight the cell in red? Something to alert that the meeting is already full.

Thanks in advance for any help or advice.

Upvotes: 0

Views: 218

Answers (1)

Kristkun
Kristkun

Reputation: 5953

If highlighting the cell in red would be enough, then you can use this custom formula in your Conditional Format Rules.

Apply to range = A1:A

Custom Formula:

=countif(A:A,A1)>=4

What it does?

  • Increment a counter if duplicates are found in the given range using COUNTIF()
  • If the number of duplicates are >= 4 then set the background color of the cell to red

Sample:

enter image description here

Another variation if you have a different list of available dates to highlight and a list of booked dates

Apply to range = A1:A

Conditional Formatting Custom Formula:

=countif(C:C,A1)>=4

enter image description here

Highlighting of cells will only be applicable in Column A. It will check how many duplicates data in Column A exist in Column C using COUNTIF()

Upvotes: 1

Related Questions