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

Question

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

Navigation

#1 by (0 votes)
#2 by (0 votes)
#3 by (0 votes)

0

I have a spreadsheet where I have some data. Of these data, I need to count the values that are repeated from a column SE the cell value of the same row, but from 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 it always returns me "Nenhum valor encontrado" even though the values match.

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

Could you help me with this formula?

excel

asked by anonymous 11.12.2017 / 20:42

3 answers

Add content at cursor position with Froala editor Firebase authentication

score 0 · Answer 1

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

Solution1

PutthefollowingformulaincolumnC:

=SE(CONTA(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 do a custom column format with the following expression:

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

score 0 · Answer 2

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

score 0 · Answer 3

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

The formula looks like this:

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

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

I hope I have helped!