0

I'm working on a spreadsheet that will have a column for dates to be entered (column A, named range "in_date"). Elsewhere on the sheet, there will be a cell with the maximum number of allowed instances of a date (H2). I'm trying to setup a conditional format to highlight all instances of any date that is equal to or greater than the maximum number listed.

I have tried using a COUNTIF but it highlights the entire column. I've tested by mixing dates and as soon as one single date is entered H2 times, the entire column is highlighted, not just the rows with the H2+ dates.

=COUNTIF(in_date, A2) > H2

Is this possible to do with COUNTIF in a conditional format? Dates will be entered at random, not necessarily consecutively and it would need to be a running highlight/count of the dates that go over the specified instance. Any help is appreciated.

Improve this question

1 Answer 1

Reset to default
0

You can create a pivot table on the column, use count as the aggregation function, then create a Low Occurrence column that consists of the row label if the count is less than or equal to the maximum, else null. Then you can do a vlookup using the original date column as the value and the Low Occurrence column as the table. Highlight the cell if this returns an error.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .