# Clustering and building Sums

#### excelactuary

##### New Member
Hi,

I have the following table:

 A B C D E F 1 3 72 2009 27,07 350 1 3 17 2013 0 250 1 3 15 2009 0 600 1 3 15 2009 0 500 1 3 79 2012 1.177,42 500 1 3 20 2010 18.012,33 460 1 3 74 2009 45,82 1.000,00 1 3 89 2014 17.926,00 1.000,00 1 3 54 2008 0 400 1 3 61 2011 405,38 500 2 3 89 2010 819,78 800 2 3 99 2009 0 250 2 4 15 2008 0 500 2 4 18 2011 0 290 2 4 20 2016 0 288 2 4 88 2011 0 422 2 4 12 2008 0 533 2 4 12 2016 1.741,94 400 2 4 67 2014 421,94 400 2 4 69 2013 438,71 400 2 4 48 2010 1.063,14 300

<tbody>
</tbody>

In the column A and B we have only two possbile values:

A: 1 and 2
B: 3 and 4

Now, in this example D4, D5 and D14 have the number 15. But, only D4 and D5 have the same corresponding entries in A, B and D (A4=A5=1, B4=B5=3, D4=D5=2009). So, for these values I would want to calculate in a new cell the following: (E4+E5)/(F4+F5).

More generally, I would like a formula which parses the column C for identical entries (for instance: looking in the whole column for 15) and then extracting all those entries which have the same corresponding values in the column A, B, and C, and in the end calculating sum(E_i)/sum(F_i) for all i entries which were extracted in the previous step.

Do you have an idea, how to implement this?

Thank you very much!

Last edited:

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### Eric W

##### MrExcel MVP
I'm a little confused about what constitutes a group. I think you might have had some typos which didn't help either. However, if I consider each unique set of the values in A:D, then I came up with this:

ABCDEFGHIJKLMNO
11372200927.07350WorkABCDCountSum(E )/Sum(F )
213172013025012.242012412200810
313152009060012.242022412201614.35485
413152009050015.132011315200920
5137920121177.4250015.242012415200810
61320201018012.3346017.132011317201310
71374200945.821.000,0018.242012418201110
813892014179261.000,0020.1320113202010139.15724
913542008040020.242022420201610
1013612011405.3850048.242012448201013.5438
1123892010819.7880054.132011354200810
1223992009025061.132011361201110.81076
1324152008050067.242012467201411.05485
1424182011029069.242012469201311.096775
1524202016028872.132011372200910.077343
1624882011042274.13201137420091#DIV/0!
1724122008053379.132011379201212.35484
18241220161741.9440088.242012488201110
1924672014421.9440089.13201138920141#DIV/0!
2024692013438.7140089.232012389201011.024725
21244820101063.1430099.232012399200910

</tbody>
Sheet1

Worksheet Formulas
CellFormula
I2=IF(\$H\$2="","",INT(MOD(H2,1)*10))
J2=IF(\$H2="","",INT(MOD(\$H2*10,1)*10))
K2=IF(\$H2="","",INT(\$H2))
L2=IF(\$H2="","",ROUND(MOD(\$H2*100,1)*10000,0))
N2=IF(\$H2="","",COUNTIFS(A:A,I2,B:B,J2,C:C,K2,D:D,L2))
O2=IF(\$H2="","",SUMIFS(E:E,A:A,I2,B:B,J2,C:C,K2,D:D,L2)/SUMIFS(F:F,A:A,I2,B:B,J2,C:C,K2,D:D,L2))

</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
H2{=MIN(\$C1:\$C21+\$A1:\$A21/10+\$B1:\$B21/100+\$D1:\$D21/1000000)}
H3{=IF(H2="","",IFERROR(SMALL(\$C\$1:\$C\$21+\$A\$1:\$A\$21/10+\$B\$1:\$B\$21/100+\$D\$1:\$D\$21/1000000,SUM(IF(\$C\$1:\$C\$21+\$A\$1:\$A\$21/10+\$B\$1:\$B\$21/100+\$D\$1:\$D\$21/1000000<=H2,1))+1),""))}

</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Set up the table as shown. Put the formula in H2, and confirm with Control+Shift+Enter. Put the formula in H3, confirm with Control+Shift+Enter, then copy it down the column as far as needed. Then put in the regular formulas in I2:O2, and copy down the columns. These formulas do require that the values in A:D are numeric. The combinations are shown in A:L, sorted by C. The count in N shows that there's only one combination in this set with more than one entry (1,2,15,2009).

Let me know if I'm on the right track.

Replies
3
Views
303
Replies
3
Views
295
Replies
6
Views
325
Replies
0
Views
295
Replies
3
Views
225

1,191,194
Messages
5,985,220
Members
439,948
Latest member
Xearo96

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back