COUNTIFS @row Resulting in 0?

Streamline Technology · 04/22/21

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 · 04/22/21

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 · 04/22/21

Do any of your invoice numbers have leading zeros?

Streamline Technology · 04/22/21

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

Paul Newcome · 04/22/21