COUNTIFS @row Resulting in 0?
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.
Best Answer
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
Do any of your invoice numbers have leading zeros?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Paul Newcome Yes. Do you know why the leading 0's would cause an issue here?
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!