Thursday, July 11, 2013

Google Spreadsheet Count Pair Combinations

Question:

( by Jonathan Zyzalo )

Hi there,

Have been using google spreadsheets to do a lot of laborious calculations lately, but I am stuck or have a mental block. Maybe I just don't have enough quite time to think about this problem in terms of the formula I need (been working on it for 3 nights now).

For simplicity, if I have rows of 4 numbers, each cell in a row can contain any number from 1 to 50. Simple example below:

1254
24810
5678

What I am wanting is to count how many times a number pair comes up, ie 2 & 4 in row1 and row2. I believe I have got it to work for each row with the following formula:

=IF(LEN($A$1:$A$3),AND(countif(A1:D1,"2"),countif(A1:D1,"4")),iferror(1/0))

But this results in a boolean TRUE or FALSE for each row (I would like an integer). I could use another countif(E1:E3, TRUE), but that is a lot of extra cells for a range of 1:50 number pairs.

I constructed a table to display the count of pairs, where xx is a space filler as a number cannot appear in a row more than once:

12345678910
1xx
2xx
3xx
4xx
5xx
6xx
7xx
8xx
9xx
10xx

Is there a way to combine my two formula so that the answer is obtained by doing the formula calculation in a 1 cell only?


Solution:

Have a look at the following screenshot:



I have the following formula in Cell G2:

=count(iferror(query(A:D;"select A where (A=2 and B=4) or (A=2 and C=4) or (A=2 and D=4) or (B=2 and A=4) or (B=2 and C=4) or (B=2 and D=4) or (C=2 and A=4) or (C=2 and B=4) or (C=2 and D=4) or (D=2 and A=4) or (D=2 and B=4) or (D=2 and C=4)")))

the above formula will count "2" and "4" in range A:D


Now, you can put any two numbers in Cell G1 and H1, and put the following formula in Cell G2:

=count(iferror(query(A:D;"select A where (A="&G1&" and B="&H1&") or (A="&G1&" and C="&H1&") or (A="&G1&" and D="&H1&") or (B="&G1&" and A="&H1&") or (B="&G1&" and C="&H1&") or (B="&G1&" and D="&H1&") or (C="&G1&" and A="&H1&") or (C="&G1&" and B="&H1&") or (C="&G1&" and D="&H1&") or (D="&G1&" and A="&H1&") or (D="&G1&" and B="&H1&") or (D="&G1&" and C="&H1&")")))


And you can also set data validation in Cells G1 and H1 to input only from numbers 1 to 50, as you have mentioned in your question..


I will also try to post another solution soon, which will have a table format as described by you with "xx" filler.


Also have a look at Solution by Option 2:

http://igoogledrive.blogspot.com/2013/07/google-spreadsheet-count-pair_11.html

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,

1 comment:

  1. Hello! I am having the same issue but with letters in A:D. I used the formula above and with the first combination was = 0 when I am certain is not. Could I get some help about it?

    ReplyDelete