Count values from one column based on a filter from another column

Asked

Viewed 4,298 times

0

I have a spreadsheet where I have some data. From this data, I need to count the repeating values of a column SE the value of the cell in the same row but in another column is equal to a given value.

I made the following formula:

SE(A:A=valor;CONT.SE(D:D; valor_a_ser_contado);"Nenhum valor encontrado")

But the same always returns to me "Nenhum valor encontrado" even if the values coincide.

Example: in the column To i have 5 values, of which 5 are equal to "10" and the other 5 are random values. On the lines where the value of To is 10, I want to count how many cells in the column D possess the value "VERDADEIRO".

Could you help me with this formula?

excel

  • It would be interesting to put an image of the spreadsheet to clarify the example.

  • I will try to make an example spreadsheet by cell phone, since the do not have access to the computer in question now and the internet where I can use it is extremely blocked.

3 answers

0

Good morning Renan Lazarotto, as you said you would like to count when two conditions happen simultaneously, the appropriate formula for this is the CONT.SES().

The formula stays like this:

=CONT.SES(A:A;valor1;D:D;valor2).

Then this formula will count how many times the value1 appears in column A at the same time as in column D the value2.

I hope I’ve helped!

0

Reading your question, I understood that you would like to implement a table that would have the following result:

inserir a descrição da imagem aqui

Solution 1

Place the following formula in column C:

=SE( CONT.SE(A:A;B2) > 0 ; CONT.SE(A:A;B2) "Value not found" )

Solution 2

Place the formula in column C

=CONT.SE(A:A;B2)

and make a custom column formatting with the following expression:

0;-0;"Valor Não Encontrado"

  • Thank you Fernando, but being greater than zero does not serve what I need. Column A contains dates that I need to specifically filter. I will test the second solution as soon as possible.

0

A simple option would be to create a column B that results in a certain value (zero or one, for example) when a certain condition is satisfied in column A. Then you do the same in column E to another variable of your interest (column D in your example). And finally, you can in a new column multiply the values of columns B and E so generate a result that can be contact by the cont function.se.

Browser other questions tagged excel

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