COUNTIFS @row Resulting in 0?

Streamline Technology

I am using a countifs function to determine if we have duplicates in an invoice tracking workflow. I am confused because some rows return a 0 result, where all rows should at least show 1. Any ideas?

=COUNTIFS([Invoice Number]:[Invoice Number], [Invoice Number]@row, [Vendor Name]:[Vendor Name], [Vendor Name]@row)

It should be able to at least count the one record @row, right? This only happens on some rows - about 90% work as expected.

Paul Newcome

Yes. Leading zeros turn the string into text whereas the ones without leading zeros are numbers. Things get a little hinky when you have mixed data types like this within the range.

I would suggest inserting a helper column and entering

=[Invoice Number]@row + ""

This will convert everything to text which will in turn provide a consistent data type. THen you would adjust your COUNTIFS to look at the helper column instead of the original column.

=COUNTIFS([Helper Invoice Number]:[Helper Invoice Number], [Helper Invoice Number]@row, [Vendor Name]:[Vendor Name], [Vendor Name]@row)

TIP: If you set that first text conversion formula as a column formula, you can then hide the helper column to keep the sheet looking clean and it will automatically apply to any new rows that are inserted.

Paul Newcome

Do any of your invoice numbers have leading zeros?

Streamline Technology

@Paul Newcome Yes. Do you know why the leading 0's would cause an issue here?

Paul Newcome

Yes. Leading zeros turn the string into text whereas the ones without leading zeros are numbers. Things get a little hinky when you have mixed data types like this within the range.

I would suggest inserting a helper column and entering

=[Invoice Number]@row + ""

This will convert everything to text which will in turn provide a consistent data type. THen you would adjust your COUNTIFS to look at the helper column instead of the original column.

=COUNTIFS([Helper Invoice Number]:[Helper Invoice Number], [Helper Invoice Number]@row, [Vendor Name]:[Vendor Name], [Vendor Name]@row)

TIP: If you set that first text conversion formula as a column formula, you can then hide the helper column to keep the sheet looking clean and it will automatically apply to any new rows that are inserted.