Saturday, July 13, 2013

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!


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

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:
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,

7 comments:

  1. The reply I posted to the forum has apparently been edited in this copy. See Adam's excellent sheet on mmult() usage at https://docs.google.com/spreadsheet/ccc?key=0AiZdNQHjqWKqdHowMlZQcUpsSEFFZVh3Wm8xdHhkV3c. --Hyde

    ReplyDelete
    Replies
    1. Hi Hyde,

      I removed the link of Adam's spreadsheet because I didn't have access to it, I have requested the access. But it seems Adam is not much active lately with his Gmail account.. And so I thought its better to remove the link...
      But it would be great if you can make a copy of Adam's Spreadsheet and share it.

      Thanks,
      Kishan.

      Delete
    2. Hi Kishan,

      Adam's mmult() sheet has always been viewable by "anyone with the link". You only need to request access if you want to edit it. Try again at:

      https://docs.google.com/spreadsheet/ccc?key=0AiZdNQHjqWKqdHowMlZQcUpsSEFFZVh3Wm8xdHhkV3c

      The forum discussion is at:

      https://productforums.google.com/d/topic/docs/HV9FIzOjC3Q/discussion

      Cheers --Hyde

      Delete
    3. Hi Hyde,

      Your Statement:
      "Adam's mmult() sheet has always been viewable by "anyone with the link". "
      is not true...

      I tried again and I don't even have viewing rights to it.
      If you want to verify my statement, then try logging out your original Google account and as you said it is "viewable by anyone with the link", then try viewing it after logging out of your account. And you may also try with your any other google account (if you have), then I am sure you wont be having right's to view it...

      Delete
  2. Thanks a lot Kishan, you did a great job and i'm happy that my problem can serves to your blog.

    Just a question : i wanted to apply this formula to shorter tables (with less players), by just erasing the isnumber() and n() in excess, but it seems it's not enough (or maybe it's just a problem of brackets haha ^^'). Don't want the precise answer, but the way to take the formulas.

    ReplyDelete
    Replies
    1. Hi Leo,

      I have updated the Blog post, read "Understanding Formula" section...

      I hope this helps you,

      Cheers!
      Kishan.

      Delete
    2. With more espace, formulas are surely more comprehensible. I get it!

      Thanks again Kishan!

      Delete