# Countif Formulae

edited 03/07/22

Hi,

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, ""))

• ✭✭✭✭✭✭

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.

https://help.smartsheet.com/function/iferror

Regards,

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

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

• ✭✭✭✭✭✭

What is the difference between your Invoice Number and Duplicate Invoice Number fields?

If you're already using the Duplicate Invoice Number field with a COUNTIF to count if a row is a duplicate, then you just need to check if [Duplicate Invoice Number]@row > 1.

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

Regards,

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

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

• Hi Jeff,

Thanks for your email - Invoice number is the actuals invoice number - the Duplicate invoice number column contatenates Invoice Number and Supplier name, to create a unique reference ID. This unique reference ID is what I am asking the formula to count. If there are two that are same... it means we are processing the same invoice for that supplier twice.

• ✭✭✭✭✭✭

OK, I see. In that case, test if

=COUNTIF([Duplicate Invoice Number]:[Duplicate Invoice Number], [Duplicate Invoice Number]@row)

works by itself.

If it does work by itself, then your issue may be with the beginning of the formula. You can try using the ISBLANK function:

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

Regards,

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

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

• Hi Jeff, I have tested it - the problem seems to be with the COUNTIF formula, it doesn't seem to count the duplicates...

• ✭✭✭✭✭✭

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.

https://help.smartsheet.com/function/iferror

Regards,

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

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

• That worked, you are star Jeff.

Thank you so much!!!

• ✭✭✭✭✭✭

Happy it worked for you. Have a great day.

Regards,

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

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!