COUNTIF duplicate based on 1 additional value, exclude blanks

Brooke R

I am essentially trying to highlight rows once there are 5 service entries submitted on a serial number using a helper checkbox column. I have tried every suggestion I saw on other questions pertaining to duplicates and blanks, but I still either get UNPARSEABLE or INCORRECT ARGUMENT.

Type Column - needs to only look at 'Service or Repair'

Serial # Column - needs to count the number of entries and the checkbox populate at 5 entries (excluding blanks).

Right now I have: =IF([Serial #]@row = " ", 0, IF(COUNTIFS(Type@row, "Service or Repair", [Serial #]:[Serial #], [Serial #]@row > 4, 1, 0))) and am returning INCORRECT ARGUMENT.

Any help would be greatly appreciated.

Nick Korna

Hi @Brooke R,

There are a couple of minor issues here:

If your first IF, delete the space between the " " - otherwise it will only examine cells with a space in (rather than a blank) to assign a 0 and you will get false positives for the empty cells from the following IF statement.

In your COUNTIFS, the Type@row should be amended to Type:Type so it looks at the whole column, not just the row. This is the main cause of getting an error as the COUNTIFS can't examine a row for one criteria and then a column for the next.

Your final formula would be:

=IF([Serial #]@row = "", 0, IF(COUNTIFS(Type:Type, "Service or Repair", [Serial #]:[Serial #], [Serial #]@row) > 4, 1, 0))

If the Serial Number is blank, 0.

If there are 5+ instances of a "Service or Repair" with the same serial number, then 1. Otherwise, 0.

Sample output:

Hope this helps, but if you've any problems/questions just post! 🙂

Nick Korna

Hi @Brooke R,

This is absolutely possible, and there are a few options on how it can look.

This first formula will flag the first instance when there are 4 other occurrences within 90 days prior.

=IF([Serial #]@row = "", 0, IF(COUNTIFS(Type:Type, "Service or Repair", [Serial #]:[Serial #], [Serial #]@row, Date:Date, <=Date@row, Date:Date, >=(Date@row - 90)) > 4, 1, 0))

Alternatively a formula could look to see if there are 5+ entries with 45 days either side (which will usually catch the one in the middle):

=IF([Serial #]@row = "", 0, IF(COUNTIFS(Type:Type, "Service or Repair", [Serial #]:[Serial #], [Serial #]@row, Date:Date, >=(Date@row - 45), Date:Date, <=(Date@row + 45)) > 4, 1, 0))

Both of these will show you that there are 5+ issues within a 3 month period, just the output would be slightly different - realistically I would use a one of a workflow alert, a filter/filtered report and/or some conditional formatting to make it very apparent when the issue comes up.

Sample data:

Hope this helps some, but if you've any questions then just post!

Nick Korna

Hi @Brooke R,

There are a couple of minor issues here:

If your first IF, delete the space between the " " - otherwise it will only examine cells with a space in (rather than a blank) to assign a 0 and you will get false positives for the empty cells from the following IF statement.

In your COUNTIFS, the Type@row should be amended to Type:Type so it looks at the whole column, not just the row. This is the main cause of getting an error as the COUNTIFS can't examine a row for one criteria and then a column for the next.

Your final formula would be:

=IF([Serial #]@row = "", 0, IF(COUNTIFS(Type:Type, "Service or Repair", [Serial #]:[Serial #], [Serial #]@row) > 4, 1, 0))

If the Serial Number is blank, 0.

If there are 5+ instances of a "Service or Repair" with the same serial number, then 1. Otherwise, 0.

Sample output:

Hope this helps, but if you've any problems/questions just post! 🙂

Brooke R

Thank you so much Nick!! That worked!

Brooke R

Hi @Nick Korna ,

Is there a way to modify this formula to only count if there are 5 service entries within a 3 month span? 5 entries over a year or more would not be concerning. However 5 entries within a 3 month time span could point to issues with a certain instrument.

Nick Korna

Hi @Brooke R,

This is absolutely possible, and there are a few options on how it can look.

This first formula will flag the first instance when there are 4 other occurrences within 90 days prior.

=IF([Serial #]@row = "", 0, IF(COUNTIFS(Type:Type, "Service or Repair", [Serial #]:[Serial #], [Serial #]@row, Date:Date, <=Date@row, Date:Date, >=(Date@row - 90)) > 4, 1, 0))

Alternatively a formula could look to see if there are 5+ entries with 45 days either side (which will usually catch the one in the middle):

=IF([Serial #]@row = "", 0, IF(COUNTIFS(Type:Type, "Service or Repair", [Serial #]:[Serial #], [Serial #]@row, Date:Date, >=(Date@row - 45), Date:Date, <=(Date@row + 45)) > 4, 1, 0))

Both of these will show you that there are 5+ issues within a 3 month period, just the output would be slightly different - realistically I would use a one of a workflow alert, a filter/filtered report and/or some conditional formatting to make it very apparent when the issue comes up.

Sample data:

Hope this helps some, but if you've any questions then just post!

Brooke R

Thank you!!