Count elements between two quantities

Asked

Viewed 1,307 times

6

I’m creating a Dashboard to a religious organization, which will include the age group of its members among other data.

The ages of registration and promotion among their religious education classes will be used as criteria.

So:

0 a 10 anos
11 a 17 anos
18 a 35 anos
36 a 50 anos
50 anos ou mais

In the spreadsheet with demographic data I created a table called RolDeMembros, your column Idade returns per row the result of the formula below:

=CONCATENAR(SOMA(ANO(HOJE())-ANO([@[Dt. Nasc.]]));" anos")

Tabela criada na planilha Rol de Membros com a coluna Idade mostrando a fórmula descrita acima

In a third spreadsheet I keep the consolidated demographic data.

In the age groups, I tried to use =CONT.SE() to calculate each of the tracks, but I am not receiving the expected data. For each track, I am using the following:

=CONT.SE(RolDeMembros[Idade];"<=10")
=CONT.SE(RolDeMembros[Idade];">=11<=17")
=CONT.SE(RolDeMembros[Idade];">=18<=35")
=CONT.SE(RolDeMembros[Idade];">=36<=50")
=CONT.SE(RolDeMembros[Idade];">=51")

The result I get is as follows::

Resultado totalmente errado das fórmulas

What is totally wrong. 169 is the number of members of the organization, over 50 years old count 62 and so on. All wrong.

Where am I going wrong?

excel office
  • 1

    Hello. Welcome to SOPT. Your question is not entirely clear because important details are missing. How you created the table RolDeMembros? From the formula you published, I was led to understand that this table has, for example, the content 32 anos. If so, comparisons of the kind "<=35" will not work because they expect a numeric field. Provides examples of the table, if possible include snapshots of the spreadsheet.

  • 1

    Thank you for your comment. The table was created by selecting the data range of the Member List sheet, including its headers, and using the Table command of the Insert tab; then, in the Design tab, I named the table as Roldemembers. On the formula, because of the function CONCATENATE() it returns, for example, 32 years. I removed the function and still did not give the expected result. As for prints, I will redo the spreadsheet separately, for contractual reasons and edit the question.

  • 1

    @Luizvieira, I put two screenshots, I hope they help to understand the situation.

  • 1

    I updated the answer, see if it fits you.

3 answers

2

Your project has two errors.

Problem 1

As already answered, you can not use more than one comparison criterion in the function CONT.SE. For that, you accurate use the function CONT.SES (which allows you to add multiple ranges and criteria). In your case the range is the same, then you repeat, and only add the "new" criterion. So, what you imagined as:

=CONT.SE(RolDeMembros[Idade];">=11<=17")

Looks like:

=CONT.SES(RolDeMembros[Idade];">=11";RolDeMembros[Idade];"<=17")

Problem 2

Problem 1 had already been answered by other colleagues, but I bet it still didn’t work for you. It’s just that there’s still the problem (that I had already commented) that the data in your column "Age" are not numerical. The function you use to assemble this column is:

=CONCATENAR(SOMA(ANO(HOJE())-ANO([@[Dt. Nasc.]]));" anos")

And the problem lies precisely in the fact that she mounts a string (of the type "35 anos") and returns in column. So the column contains text and not numbers. That’s why your comparison doesn’t work. Ideally, keep only the same numbers in this column. What is simple, just change your function above to:

=SOMA(ANO(HOJE())-ANO([@[Dt. Nasc.]]))

The "problem" of not displaying to the user the text "years" in front is simple to be solved in formatting. Access the cell formatting (right click, choose "Format cells"), go to custom formatting, and type 00 "anos":

inserir a descrição da imagem aqui

Ready! So the formulas using CONT.SES will work properly:

inserir a descrição da imagem aqui

  • 1

    thanks for your reply. After your comment to my question, said he had removed the CONCATENATE() function without, however, having the expected result. And since the answers I have used the function CONT.SES() for all tracks except the first, without yet having the expected result I will try the updated answers. Again, thank you..

  • 1

    Not at all. But if it didn’t work with the column with numerical values, you’ve got some other little mistake out there. Maybe it’s worth a general overhaul or even rewriting the formula.

0

Right, as there is text in your formula, ideal will be to create a new column with only the numbers (ages) to create the desired frame.

From there use the following formulas:

=CONT.SE(RolDeMembros[Idade];"<"&11)
=CONT.SES(RolDeMembros[Idade];">="&11;RolDeMembros[Idade];"<"&18)
=CONT.SES(RolDeMembros[Idade];">="&18;RolDeMembros[Idade];"<"&36)
=CONT.SES(RolDeMembros[Idade];">="&36;RolDeMembros[Idade];"<"&51)
=CONT.SE(RolDeMembros[Idade];">="&51)

You can use the number together as you did "<=", however I used the & (And commercial) so you can have an age group table.

Take this example:

https://support.office.com/pt-br/article/CONT-SES-Fun%25C3%25A7%25C3%25A3o-CONT-SES-dda3dc6e-f74e-4aee-88bc-aa8c2a866842? ui=en-Br&rs=en-Br&ad=Br&fromar=1

I used something similar in this spreadsheet with the price range of CAESB (DF Water Supply Service).

https://github.com/excelguru/compara_valor_agua

Another thing you can use is the type of formula for calculating age, see some more common examples provided by Microsoft:

https://support.office.com/pt-br/article/Calcular-idade-113d599f-5fea-448f-a4c3-268927911b37?ui=pt-BR&rs=pt-BR&ad=BR&fromAR=1

  • Thank you for your reply. I needed to edit your formula for =CONT.SES(RolDeMembros[Idade];">="&11;RolDeMembros[Idade];"<="&17) and following, but it didn’t work. I had already read this support article, but unsuccessfully too. I’ll study the spreadsheet you linked to see if I can find a solution.

  • @Joaosantana That’s right, I’m sorry, I’m not with Excel in this mask so I couldn’t test it. Hug good luck there.

0

To process the alphanumeric data displayed in the spreadsheet, replace "age" with:

ESQUERDA(idade;PROCURAR(" ";idade)-1)

This formula takes the text " years" and only the numerical part of the age will be considered.

The formulas would look like this in my example, for the alphanumeric data you’re using for age.

=CONT.SE(ESQUERDA(Idade;PROCURAR(" ";Idade)-1);"<=10")
=CONT.SES(ESQUERDA(Idade;PROCURAR(" ";Idade)-1);">=11";ESQUERDA(Idade;PROCURAR(" ";Idade)-1);"<18")
=CONT.SES(ESQUERDA(Idade;PROCURAR(" ";Idade)-1);">=18";ESQUERDA(Idade;PROCURAR(" ";Idade)-1);"<35")
=CONT.SES(ESQUERDA(Idade;PROCURAR(" ";Idade)-1);">=36";ESQUERDA(Idade;PROCURAR(" ";Idade)-1);"<50")
=CONT.SE(ESQUERDA(Idade;PROCURAR(" ";Idade)-1);">=51")

Just adapt your formulas to apply in the table.

The example below is without table application and with numerical data for ages:

Select the column with ages, and at the top left of the Excel, where the active cell address appears (column and row, for example B22), type the column name: Age

I did it for the data below:

inserir a descrição da imagem aqui

In another FLAP put the following formulas, which in the example below already present the result to the right of them:

=CONT.SE(Idade;"<=10")                    10
=CONT.SES(Idade;">=11";Idade;"<18")        7
=CONT.SES(Idade;">=18";Idade;"<35")       17
=CONT.SES(Idade;">=36";Idade;"<50")       14
=CONT.SE(Idade;">=51")                    10

Note that when naming the data range (equal to the column selected in this case), Excel also understands what FLAP they belong.

If the term "spreadsheet" indicates that you are using separate files (XLSX), just adapt what I have presented.

That’s it.

  • 1

    Thank you for your reply, @Leo. When I say spreadsheet, I’m referring to the worksheets in the same workbook, which in your answer you call a flap. As for the procedure you describe, when creating a table, your headers are named and can be referenced in the same way, as I even wrote in the question. I repeated, finally, the structure you described but, still, it returns to me 0; Could you pass me the spreadsheet where you got this result, for comparison?

  • 1

    Hi @João, cool, the spreadsheet has exactly what is described in the answer, I used two TABS for this (with ABA it is clearer to me that it is the same file, or in my case, the same spreadsheet, but it is the habit), the first with the formulas and the second with the numbers in column named and starting from the value 1 in row 1. If you cannot, return.

  • @João, I edited my answer that I believe will solve your problem without changing your data table.

  • OI @Joãosantana, I edited the answer again, there was an unnecessary part in the last line of the last formula that I had not noticed when I posted, both in the initial part for alphanumeric data and in the previous part that I kept, but the functionality was correct. Did any of the answers here help you or direct you to solving the problem? If yes, please mark the answer.with the "check" below the score of this answer. Good job.

Browser other questions tagged excel office

You are not signed in. Login or sign up in order to post.