COUNTIF duplicate based on 1 additional value, exclude blanks

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.

Tags:

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    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! 🙂

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    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! 🙂

  • Thank you so much Nick!! That worked!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!