Define separate cells instead of a range in Excel formula

Navigation
4

Suppose I have the following table: 

   |-----|-----|-----|-----|-----|-----|
   |  A  |  B  |  C  |  D  |  E  |  F  |
   |-----|-----|-----|-----|-----|-----|
1  |  s  |  n  |  n  |  s  |  s  |  s  |
   |-----|-----|-----|-----|-----|-----|
2  |  n  |  n  |  s  |  s  |  n  |  n  |
   |-----|-----|-----|-----|-----|-----|

I want to count the number of s 's that appear in a row. I know that to count all that appear in fila 1 I use: 

=CONTAR.SI(A1:F1,"s")

but this is to count cells in a range A1:F1 , count A1, B1, C12, D1, E1, F1.

What I want is to count cells that are separated non-uniformly, for example count A1, D1 and F1. I tried: 

 =CONTAR.SI(A1,D1,F1,"s")

but it does not work. How can I achieve this?

    
asked by Rene Limon 02.03.2017 в 16:55
source

3 answers

0

I found a solution, I do not know if it is the most practical (or readable) but it works. In the case that I propose adding A1, D1 and F1, the formula would be: 

 =SI((ESNUMERO(HALLAR("si",A1))),1,0)
 +SI((ESNUMERO(HALLAR("si",D1))),1,0)
 +SI((ESNUMERO(HALLAR("si",F1))),1,0)

the result that returns is 3 (that formula should go in a single line but this is more readable).

    
answered by 02.03.2017 / 22:01
source
0

You can always create a subtable with the values you want and perform the count on that table: 

{A3} =A1
{B3} =E1
{C3} =G1

=CONTAR.SI(A3:C3,"s")

There is no formula that allows you to check scattered cells. Excel only works with ranges. Another thing is that you wanted to take into account only subranges ... in which case you could choose to add your results: 

=CONTAR.SI(A1:B2,"s")+CONTAR.SI(D1:G1,"s")

An alternative is to create a parallel table that indicates what values you want to take into account and make use of the SUMAR.SI function: 

   |-----|-----|-----|-----|-----|-----|
   |  A  |  B  |  C  |  D  |  F  |  G  |
   |-----|-----|-----|-----|-----|-----|
1  |  s  |  n  |  n  |  s  |  s  |  s  |
   |-----|-----|-----|-----|-----|-----|
2  |  n  |  n  |  s  |  s  |  n  |  n  |
   |-----|-----|-----|-----|-----|-----|
3  |  1  |  1  |     |  1  |     |     |
   |-----|-----|-----|-----|-----|-----|
4  |     |     |     |  1  |     |     |
   |-----|-----|-----|-----|-----|-----|

In the case of the example, we would only be interested in the values of A1, B1, D1 and D2. The formula would look like this: 

=SUMAR.SI(A1:G2,"s",A3:G4);

If you want to give more weight to some result, just put a higher value.

    
answered by 02.03.2017 в 17:04
0

Use matrices. Example 

=SUM(COUNTIF(INDIRECT({"A1","D1","F1"}),"s"))

References

answered by 08.03.2017 в 00:46
click button with selenium Login by Api Token Laravel 5.4