How to use countif with digits that start with 0?
Hi guys good morning. I have a difficulty with a formula. In the HR area of the company I work for, we use a form to register for vacancies offered throughout Brazil and in this form we request the CPF (Individual Taxpayer Registration Number - number we use to identify a person in Brazil). This number consists of 11 digits, and you can START with any digit, including 0. I would like to make a simple analysis: to identify if the candidate in question is already in our candidate base. I tried to use COUNTIF to count the amount of that specific value across the column. However, it is only working for CPFs that start with digits from 1 to 9. CPFs that start with 0 are not being identified. I would like some help, please. Thanks.
Olá pessoal, bom dia.
Estou com uma dificuldade em uma fórmula.
Na área de RH da empresa que trabalho utilizamos um formulário para cadastro em vagas ofertadas por todo o Brasil e neste formulário solicitamos o número CPF (Cadastro de Pessoa Física - número que utilizamos para identificar uma pessoa no Brasil). Este número é composto de 11 dígitos, podendo INICIAR com qualquer dígito, inclusive o 0.
Gostaria de fazer uma análise simples: identificar se o candidato em questão já consta em nossa base de candidatos.
Tentei utilizar o COUNTIF para contar a quantidade daquele valor específico em toda a coluna. Porém, só está funcionando para os CPF que começam com dígitos de 1 a 9. Os CPF que começam com 0 não estão sendo identificados.
Gostaria de uma ajuda, por favor. Obrigado.
Answers
-
All values being looked at need to be of the same data type. Numbers that start with 1 - 9 are being stored as numerical values, but those that start with 0 are being stored as text values (so the leading zero won't drop off).
To accommodate this, create another text/number column (that can be hidden after setting up to keep the sheet looking clean) and use
=[ID Number Column Name]@row + ""
This will convert everything to text values. Now you can run your COUNTIFS off of this helper column, and you should be getting the correct results.
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!
-
Hi Paul, I'm sorry for the delay in replying. I was unable to solve the problem with the suggested solution. But, I added an auxiliary column with the digit 1 and added it with the CPF column. In this way it was possible to do the necessary counting and identification. Thank you very much for your response and help. Big hug.
-
Are you able to provide more detail surrounding your auxiliary column and adding the 1? Exactly how did you do this? Can you provide an example?
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!
-
Hi Everyone!
Can you please help me with this. I'm facing the same problem with my sheet.
These are the formulas I'm using for both counting (helper) and "YES" and "NO" columns.
Any thoughts?
Thank you in advance
-
Hi @Sadsoft
I've seen this before when there are leading 0's in a Primary Column.
Try using @cell = before the criteria:
=COUNTIF([Column Name]:[Column Name]; @cell = [Column Name]@row)
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
-
Wonderful! Thanks for letting me know 🙂
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Hi, everyone!
Hi @Genevieve P. !
I would like to understand what is the implication/meaning of the use of the "@cell" in the formula.
Also would like to understand how to avoid empty cells to be counted.
Thank you in advance.
-
Hi @Sadsoft
Can you post the current formula you're using?
If you're just using COUNT, it will automatically exclude blank cells. If you're using COUNTIF, it will only count the values you tell it to (ex. cells that equal "This", which would exclude blank cells).
The @cell tells the formula to check each cell in the previously stated range to see if that specific cell = the value. See: Create Efficient Formulas with @cell and @row
Cheers!
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
-
Hi @Sadsoft
Thank you for clarifying!
Since you're specifically looking for a blank cell, your formula will COUNT how many blank cells there are. This is because you're searching for [Referência do Documento]@row, which is blank.
You can add an IF Statement at the beginning to return Blank if the cell is blank:
=IF(Referência do Documento]@row = ""; ""; IF(COUNTIF([Referência do Documento]:[Referência do Documento]; @cell = [Referência do Documento]@row) > 1; "SIM"; "NÃO"))
=IF(Referência do Documento]@row = ""; ""; COUNTIF([Referência do Documento]:[Referência do Documento]; @cell = [Referência do Documento]@row))
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
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!