Countif Formulae

Deepa A

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

Jeff Reisman

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

Jeff Reisman

@Deepa A

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

Deepa A

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.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/321783#Comment_321783

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

Deepa A

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

Jeff Reisman

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

Deepa A

That worked, you are star Jeff.

Thank you so much!!!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/321794#Comment_321794

Happy it worked for you. Have a great day.