Google Spreadsheet Counting number of Win and Loss in a single formula

Question:

( by Léo Dumora )

Hi guys,

I've created a sheet for the exemple :

In B18, I want to get the number of filled cells, for the personn concerned, here "Bobo", on the array B3:W14.

Of course there will be all the names, so the function will have to look for the personn (A18) in the range (A3:A14), and then count the filled cells for this personn.

I hope it's enough clear with my poor english

Thanks in advance

=============

Hyde's Reply:

To get a count of Xs:

=countif( filter(B3:X14; A3:A14 = A18); "x" )

To count all non-blank cells:

=counta( filter(B3:X14; A3:A14 = A18) )

To create a list of all names and count the Xs and calculate the sum of numbers that appear on a names' row:

=unique(A3:A13)

=arrayformula( mmult( B3:X13 = "x"; transpose(sign(column(B3:X13))) ) )

=arrayformula( mmult( n(B3:X13); transpose(sign(column(B3:X13))) ) )

Cheers --Hyde

===========

Kishan's Reply:

Hi ,

In addition to Hyde's contribution:

Try the following single formula solutions:

option 1:

=query(arrayformula(if({{1};{0};{0};{0};{0};{0};{0};{0};{0};{0};{0};{0};{0};{0};{0};{0};{0};{0};{0};{0};{0};{0};{0}};A3:A13;n(A3:W13)));"select Col1,Col2+Col3+Col4+Col5+Col6+Col7+Col8+Col9+Col10+Col11+Col12+Col13+Col14+Col15+Col16+Col17+Col18+Col19+Col20+Col21+Col22+Col23,Col2+Col4+Col6+Col8+Col10+Col12+Col14+Col16+Col18+Col20+Col22,Col3+Col5+Col7+Col9+Col11+Col13+Col15+Col17+Col19+Col21+Col23 label Col1 'Name',Col2+Col3+Col4+Col5+Col6+Col7+Col8+Col9+Col10+Col11+Col12+Col13+Col14+Col15+Col16+Col17+Col18+Col19+Col20+Col21+Col22+Col23 'Total',Col2+Col4+Col6+Col8+Col10+Col12+Col14+Col16+Col18+Col20+Col22 'Gagné',Col3+Col5+Col7+Col9+Col11+Col13+Col15+Col17+Col19+Col21+Col23 'Perdu' ")

and

option 2:

=arrayformula(if({{1};{0}};A3:A13; n(B3:B13)+n(C3:C13)+n(D3:D13)+n(E3:E13)+n(F3:F13)+n(G3:G13)+n(H3:H13)+n(I3:I13)+n(J3:J13)+n(K3:K13)+n(L3:L13)+n(M3:M13)+n(N3:N13)+n(O3:O13)+n(P3:P13)+n(Q3:Q13)+n(R3:R13)+n(S3:S13)+n(T3:T13)+n(U3:U13)+n(V3:V13)+n(W3:W13) ))

Cheers!

Kishan.

================

Leo's Reply with a question:

Hi Hyde,

Thanks a lot for your answer! I've applied it well on my work sheet.

I was writing others questions but Kishan solved them live on the worksheet (just look at it).

But i'm a little embarassed, and i didn't want to ask Kishan, but i have to mix both solutions you gave me. In fact I want 3 columns :

- the (number of filled cells (your 18) / 2) - 1 (to not count the X) : 8 for Bobo

- 2 columns, Gagné & Perdu (what Kishan did great).

to sum up, for Bobo (and others) : 8 - 19 - 13

But with your formula and the Kishan's one, i've some pain to mix the solutions.

===================

Solution:

Try this:

=arrayformula(if({{1};{0};{0};{0}};A3:A13;if({{0};{1};{0};{0}};(isnumber(B3:B13)+isnumber(C3:C13)+isnumber(D3:D13)+isnumber(E3:E13)+isnumber(F3:F13)+isnumber(G3:G13)+isnumber(H3:H13)+isnumber(I3:I13)+isnumber(J3:J13)+isnumber(K3:K13)+isnumber(L3:L13)+isnumber(M3:M13)+isnumber(N3:N13)+isnumber(O3:O13)+isnumber(P3:P13)+isnumber(Q3:Q13)+isnumber(R3:R13)+isnumber(S3:S13)+isnumber(T3:T13)+isnumber(U3:U13)+isnumber(V3:V13)+isnumber(W3:W13))/2; if({{0};{0};{1};{0}};n(B3:B13)+n(D3:D13)+n(F3:F13)+n(H3:H13)+n(J3:J13)+n(L3:L13)+n(N3:N13)+n(P3:P13)+n(R3:R13)+n(T3:T13)+n(V3:V13) ; n(C3:C13)+n(E3:E13)+n(G3:G13)+n(I3:I13)+n(K3:K13)+n(M3:M13)+n(O3:O13)+n(Q3:Q13)+n(S3:S13)+n(U3:U13)+n(W3:W13) ))))


the above complex formula will give you the following results:

=============

Understanding Formula:

**********************

=arrayformula(

if({{1};{0};{0};{0}};

A3:A13;

if({{0};{1};{0};{0}};

(isnumber(B3:B13)+isnumber(C3:C13)+isnumber(D3:D13)+isnumber(E3:E13)+isnumber(F3:F13)+isnumber(G3:G13)+isnumber(H3:H13)+isnumber(I3:I13)+isnumber(J3:J13)+isnumber(K3:K13)+isnumber(L3:L13)+isnumber(M3:M13)+isnumber(N3:N13)+isnumber(O3:O13)+isnumber(P3:P13)+isnumber(Q3:Q13)+isnumber(R3:R13)+isnumber(S3:S13)+isnumber(T3:T13)+isnumber(U3:U13)+isnumber(V3:V13)+isnumber(W3:W13))/2;

if({{0};{0};{1};{0}};

n(B3:B13)+n(D3:D13)+n(F3:F13)+n(H3:H13)+n(J3:J13)+n(L3:L13)+n(N3:N13)+n(P3:P13)+n(R3:R13)+n(T3:T13)+n(V3:V13);

n(C3:C13)+n(E3:E13)+n(G3:G13)+n(I3:I13)+n(K3:K13)+n(M3:M13)+n(O3:O13)+n(Q3:Q13)+n(S3:S13)+n(U3:U13)+n(W3:W13)

)

)

)

)

**********************

the above fomula is working like:

=arrayformula(

if({{1};{0};{0};{0}};

"first column";

if({{0};{1};{0};{0}};

"second column";

if({{0};{0};{1};{0}};

"third column" ;

"fourth column"

)

)

)

)

**********************

Now "first column" formula is:

A3:A13

Similarly "second column" formula is:

(isnumber(B3:B13)+isnumber(C3:C13)+isnumber(D3:D13)+isnumber(E3:E13)+isnumber(F3:F13)+isnumber(G3:G13)+isnumber(H3:H13)+isnumber(I3:I13)+isnumber(J3:J13)+isnumber(K3:K13)+isnumber(L3:L13)+isnumber(M3:M13)+isnumber(N3:N13)+isnumber(O3:O13)+isnumber(P3:P13)+isnumber(Q3:Q13)+isnumber(R3:R13)+isnumber(S3:S13)+isnumber(T3:T13)+isnumber(U3:U13)+isnumber(V3:V13)+isnumber(W3:W13))/2

Similarly "third column" formula is:

n(B3:B13)+n(D3:D13)+n(F3:F13)+n(H3:H13)+n(J3:J13)+n(L3:L13)+n(N3:N13)+n(P3:P13)+n(R3:R13)+n(T3:T13)+n(V3:V13)

And "fourth column" formula is:

n(C3:C13)+n(E3:E13)+n(G3:G13)+n(I3:I13)+n(K3:K13)+n(M3:M13)+n(O3:O13)+n(Q3:Q13)+n(S3:S13)+n(U3:U13)+n(W3:W13)



I hope you can now understand the formula and modify it as per your requirement.


=============
I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive