Number as text throwing Formula off

Hi All,
I created an invoice tracker. To identify duplicate invoices I created an "identifier column" with a formula. (=IF(COUNTIF ([invoice #] : [Invoice #], [Invoice #]@row) >1, "Yes", "No") and subsequent conditional formatting if it is "Yes".
I've run into a snag. Invoice #'s come in all shapes and sizes and it seemed to be working fine, even when Invoice numbers started with letters or words.
Some of our invoices are starting with one or multiple zeros. When it is typed in it converts it to text by adding the ' in front of it. But because of that, it is not identifying duplicate invoices.
I can't take it out. It's the same if there is one 0 or six 0's.
How can I change my formula so it reads these as well?
Best Answer
-
Hi @LDP
Can you test to see if using "@cell = value" in your formula makes a difference? I've seen this help with leading 0's before:
=IF(COUNTIF([Invoice #]:[Invoice #], @cell = [Invoice #]@row) >1, "Yes", "No")
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
Answers
-
Hi @LDP
Can you test to see if using "@cell = value" in your formula makes a difference? I've seen this help with leading 0's before:
=IF(COUNTIF([Invoice #]:[Invoice #], @cell = [Invoice #]@row) >1, "Yes", "No")
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
-
That worked!!! Thank you so much!!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66K Get Help
- 429 Global Discussions
- 149 Industry Talk
- 488 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 152 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!