SumIFs for two tables

Nissepiano

New Member
Joined
Nov 1, 2019
Messages
1
Hi everyone!

I need some help.

I have two tables (short version, see below). And I want put these together with the and want to have the total sum (from table 1) for the categories in table 2. The R and Z is the positions in a grid. So I want to sum up the weights in table 1 for every category in table 2 in new table total weights per category. I hope someone can help me, I can do it a via several tables, but it is not so nice with so many tables.

Regards
Nisspeiano


Weights
R393.333420446.667473.333
Z
7.6547584621659
44.552116225424012544
103.252116225424012544
161.952116225424012544
211.5331458155516511753
2521458155516511753
292.4671458155516511753

<colgroup><col span="4"><col></colgroup><tbody>
</tbody>


Categories
393.333420446.667473.333
7.6LLW-2aLLW-1RISKLOW RISK
44.55LLW-2aLLW-1RISKLOW RISK
103.25LLW-2aLLW-1RISKLOW RISK
161.95LLW-2aLLW-1RISKLOW RISK
211.533LLW-2bLLW-2aLLW-1LOW RISK
252LLW-2bLLW-2aLLW-1RISK
292.467LLW-3LLW-2aLLW-1RISK

<colgroup><col span="4"><col></colgroup><tbody>
</tbody>
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
611
Hi,

Is this what you needed?

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Weights</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">R</td><td style="text-align: right;;">393.333</td><td style="text-align: right;;">420</td><td style="text-align: right;;">446.667</td><td style="text-align: right;;">473.333</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Z</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">7.6</td><td style="text-align: right;;">547</td><td style="text-align: right;;">584</td><td style="text-align: right;;">621</td><td style="text-align: right;;">659</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">44.55</td><td style="text-align: right;;">2116</td><td style="text-align: right;;">2254</td><td style="text-align: right;;">2401</td><td style="text-align: right;;">2544</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">103.25</td><td style="text-align: right;;">2116</td><td style="text-align: right;;">2254</td><td style="text-align: right;;">2401</td><td style="text-align: right;;">2544</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">161.95</td><td style="text-align: right;;">2116</td><td style="text-align: right;;">2254</td><td style="text-align: right;;">2401</td><td style="text-align: right;;">2544</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">211.533</td><td style="text-align: right;;">1458</td><td style="text-align: right;;">1555</td><td style="text-align: right;;">1651</td><td style="text-align: right;;">1753</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">252</td><td style="text-align: right;;">1458</td><td style="text-align: right;;">1555</td><td style="text-align: right;;">1651</td><td style="text-align: right;;">1753</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">292.467</td><td style="text-align: right;;">1458</td><td style="text-align: right;;">1555</td><td style="text-align: right;;">1651</td><td style="text-align: right;;">1753</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Categories</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;">393.333</td><td style="text-align: right;;">420</td><td style="text-align: right;;">446.667</td><td style="text-align: right;;">473.333</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;">7.6</td><td style=";">LLW-2a</td><td style=";">LLW-1</td><td style=";">RISK</td><td style=";">LOW RISK</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;">44.55</td><td style=";">LLW-2a</td><td style=";">LLW-1</td><td style=";">RISK</td><td style=";">LOW RISK</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;">103.25</td><td style=";">LLW-2a</td><td style=";">LLW-1</td><td style=";">RISK</td><td style=";">LOW RISK</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;;">161.95</td><td style=";">LLW-2a</td><td style=";">LLW-1</td><td style=";">RISK</td><td style=";">LOW RISK</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="text-align: right;;">211.533</td><td style=";">LLW-2b</td><td style=";">LLW-2a</td><td style=";">LLW-1</td><td style=";">LOW RISK</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style="text-align: right;;">252</td><td style=";">LLW-2b</td><td style=";">LLW-2a</td><td style=";">LLW-1</td><td style=";">RISK</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style="text-align: right;;">292.467</td><td style=";">LLW-3</td><td style=";">LLW-2a</td><td style=";">LLW-1</td><td style=";">RISK</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style=";">Categories</td><td style=";">Weights</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style=";">LLW-1</td><td style="text-align: right;;">12,299</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style=";">LLW-2a</td><td style="text-align: right;;">11,560</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">25</td><td style=";">LLW-2b</td><td style="text-align: right;;">2,916</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">26</td><td style=";">LLW-3</td><td style="text-align: right;;">1,458</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">27</td><td style=";">LOW RISK</td><td style="text-align: right;;">10,044</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">28</td><td style=";">RISK</td><td style="text-align: right;;">11,330</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B23</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">$B$14:$E$20=A23</font>)*$B$4:$E$10</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

Similar threads

Watch MrExcel Video

Forum statistics

Threads
1,129,467
Messages
5,636,462
Members
416,919
Latest member
twc2c

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top