Countif Formulae

Deepa A
Deepa A
edited 03/07/22 in Formulas and Functions


I am trying this formulae - it worked fine in one sheet but doesn't seem to work in another sheet even though it has the same column manes.

This formula should ideally activate checkbox if there are duplicates and then I have a conditional formatting that will highlight the duplicate Invoice numbers when checkbox is checked.

Currently the check box isn't getting checked and also the formula is bringing back 'NO MATCH'

Any help would be appreciated.

=IF([Invoice Number]@row = "", "", IF(COUNTIF([Duplicate Invoice Number]:[Duplicate Invoice Number], [Duplicate Invoice Number]@row) > 1, 1, ""))

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    So your problem then is likely something to do with data in the Duplicate Invoice Number column.

    What formula are you using to concatenate the Invoice Number and Supplier name? Is that formula throwing errors anywhere in the column? If there's an error in the column, the COUNTIFS will have a problem as well. Consider wrapping your concatenate formula in IFERROR in order to clear away errors and let your COUNTIFS do its job.


    Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!