A combination of four columns

[bines53]

Hello friends ,


I have a file, which checks all combinations of two columns, I want to extend it to a combination of 4 columns, what I need to change formulas (in red)?

Thank you !

 

[Chihiro]

Not exactly sure if I understood your requirement. But I'm assuming you want to find frequency for each 4 column combination?

Easiest method is to create helper column which concatenates A~D columns.
Create pivot table and count occurrence of combo. See attached.

 

[bines53]

Hi Chihiro ,

I'm not sure exactly how many combinations, but the combination of first and last, so to simplify it
=COUNTIFS(A10:A110,A1,B10:B110,B1,C10:C110,C1,D10:D110,D1)
=COUNTIFS(D10:D110,A1,E10:E110,B1,F10:F110,C1,G10:G110,D1)

If I'm not mistaken, a total of 35 combinations.

Thank you !

 

[Chihiro]

Another method is to Copy the table to another sheet and Remove Duplicates selecting Column A~D only.

Then use following formula to calculate count of each combination.
=SUM(IF(גיליון1!$A$10:$A$110&גיליון1!$B$10:$B$110&גיליון1!$C$10:$C$110&גיליון1!$D$10:$D$110=A2&B2&C2&D2,1,0))

Entered as array (CSE).

You can do another for D~G.

You have Column D overlapping. Is this intentional?

Either case, you see at bottom of Combo columns count of unique combo.
A~D = 33
D~G = 32

See attached (Sheet1 for formula)

 

[bines53]

Hi Chihiro ,

Probably not explained correctly, there are 35 possible combinations, is there a formula like the example I attached, if I may build combinations that,Each row represents four different columns.

1 2 3 4
1 2 3 5
1 2 3 6
1 2 3 7
1 2 4 5
1 2 4 6
1 2 4 7
1 2 5 6
1 2 5 7
1 2 6 7
1 3 4 5
1 3 4 6
1 3 4 7
1 3 5 6
1 3 5 7
1 3 6 7
1 4 5 6
1 4 5 7
1 4 6 7
1 5 6 7
2 3 4 5
2 3 4 6
2 3 4 7
2 3 5 6
2 3 5 7
2 3 6 7
2 4 5 6
2 4 5 7
2 4 6 7
2 5 6 7
3 4 5 6
3 4 5 7
3 4 6 7
3 5 6 7
4 5 6 7

 

[Chihiro]

So, you want to count if all cell value for 4 column combo >0?

Then what you can do is store column combo shown in your post as reference point and use INDIRECT() & ADDRESS() to make it dynamic.

So assuming Column combo are stored in Column J:M starting at row #12.
=COUNTIFS(INDIRECT(ADDRESS(10,J12)&":"&ADDRESS(110,J12)),">0",INDIRECT(ADDRESS(10,K12)&":"&ADDRESS(110,K12)),">0",INDIRECT(ADDRESS(10,L12)&":"&ADDRESS(110,L12)),">0",INDIRECT(ADDRESS(10,M12)&":"&ADDRESS(110,M12)),">0")

Copy down.

See attached.

 

[bines53]

Hi Chihiro ,

Thanks to the formula!
It is possible without volatile functions ,As formula in cell J4 ?

David

 

[Chihiro]

Not without hard coding some column references at least (it will not be fully dynamic).

Other method is to use Offset, but it's still volatile function.