How to use countif with digits that start with 0?

Arthur Magalhães · 01/14/21

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.

Paul Newcome · 01/14/21

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.

Arthur Magalhães · 02/04/21

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).<\/p>

To accommodate this, create another text\/number column (that can be hidden after setting up to keep the sheet looking clean) and use<\/p>

=[ID Number Column Name]@row + ""<\/p>

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.<\/p>","bodyRaw":"[{\"insert\":\"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).\\n\\nTo accommodate this, create another text\\\/number column (that can be hidden after setting up to keep the sheet looking clean) and use\\n=[ID Number Column Name]@row + \\\"\\\"\\n\\nThis 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.\\n\"}]","format":"rich","dateInserted":"2021-01-14T13:53:59+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-26T20:54:28+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/275225#Comment_275225","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/275225#Comment_275225

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.

Paul Newcome · 02/05/21

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?

Sadsoft · 04/28/22

Hi@Arthur Magalhães!

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

Genevieve P. · 04/28/22

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

Sadsoft · 04/28/22

Hi @Sadsoft<\/a> <\/p>

I've seen this before when there are leading 0's in a Primary Column. <\/p>

Try using @cell =<\/strong> before the criteria:<\/p>

=COUNTIF([Column Name]:[Column Name]; @cell = [Column Name]@row<\/strong>)<\/p>

Cheers,<\/p>

Genevieve<\/p>","bodyRaw":"[{\"insert\":\"Hi \"},{\"insert\":{\"mention\":{\"name\":\"Sadsoft\",\"userID\":147468}}},{\"insert\":\" \\nI've seen this before when there are leading 0's in a Primary Column. \\nTry using\"},{\"attributes\":{\"bold\":true},\"insert\":\" @cell =\"},{\"insert\":\" before the criteria:\\n=COUNTIF([Column Name]:[Column Name]; \"},{\"attributes\":{\"bold\":true},\"insert\":\"@cell = [Column Name]@row\"},{\"insert\":\")\\nCheers,\\nGenevieve\\n\"}]","format":"rich","dateInserted":"2022-04-28T14:37:59+00:00","insertUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-06-26T16:24:30+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/327705#Comment_327705","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/327705#Comment_327705

Hi,@Genevieve P.!

It worked!!!

Thank you so much!

Cheers,

Sadsoft

Genevieve P. · 04/28/22

Wonderful! Thanks for letting me know

Sadsoft · 05/16/22

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.

Genevieve P. · 05/17/22

Hi, everyone!<\/p>

Hi @Genevieve P.<\/a> !<\/p>

I would like to understand what is the implication\/meaning of the use of the "@cell" in the formula.<\/p>

Also would like to understand how to avoid empty cells to be counted.<\/p>

Thank you in advance.<\/p>","bodyRaw":"[{\"insert\":\"Hi, everyone!\\nHi \"},{\"insert\":{\"mention\":{\"name\":\"Genevieve P.\",\"userID\":91566}}},{\"insert\":\" !\\nI would like to understand what is the implication\\\/meaning of the use of the \\\"@cell\\\" in the formula.\\nAlso would like to understand how to avoid empty cells to be counted.\\nThank you in advance.\\n\\n\"}]","format":"rich","dateInserted":"2022-05-16T18:31:27+00:00","insertUser":{"userID":147468,"name":"Sadsoft","url":"https:\/\/community.smartsheet.com\/profile\/Sadsoft","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-05-23T07:39:21+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/329775#Comment_329775","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/329775#Comment_329775

Hi@Sadsoft

Can you post the current formula you're using?

如果你只是使用计数,它会自动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 checkeach cellin the previously stated range to see if that specific cell = the value. See:Create Efficient Formulas with @cell and @row

Cheers!

Genevieve

Sadsoft · 05/17/22

Genevieve P. · 05/17/22

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

How to use countif with digits that start with 0?

Answers

Help Article Resources

Categories