I am receiving a # NO MATCH error when trying to find duplicates. I am using the following formula.

=COUNTIFS([IMS Barcode]:[IMS Barcode], [IMS Barcode]@row)


  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 03/05/24

    Hi @Amber K.,

    the following is your formula:

    =COUNTIFS([IMS Barcode]:[IMS Barcode], [IMS Barcode]@row)

    this is a screenshot shows the result:

    PMP Certified


    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • I copied and pasted the above formula. I continue to receive a no match error.

  • KPH
    KPH ✭✭✭✭✭✭

    Hi @Amber K.

    The formula looks fine, and as @Bassam Khalil showed, worked for him. I think the issue is in the data. Does your IMS Barcode column contain any cells that have #NO MATCH in them? With the COUNTIFS formula, if the data that is being counted contains errors, those errors sort of bubble up into the COUNTIFS. If you have formula populating the IMS Barcode column and they are returning #NO MATCH, try wrapping those in an IFERROR function. Ie =IFERROR(original formula,""). This will return "" (nothing) instead of the error, which should mean your COUNTIFS functions as expected.

    Let us know how you get on.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!