Formula to sum the volume of a list of items against another item sheet

Kingkoopa

Board Regular
Joined
Aug 7, 2014
Messages
94
Hi Guys,

I am currently on windows 7 and using Excel 2016.

I have 3 sheets with following data :

Sheet 1:

Delivered ShipVolume
Ship120
Ship270
Ship315
Ship430
Ship520
Ship60
Ship750
Ship815
Ship960
Ship105

<tbody>
</tbody>


Sheet 2

Invoiced Ship
Ship2
Ship7
Ship4
Ship3

<tbody>
</tbody>
Sheet 3
Total Invoice: ??

I would like to get the total volume of invoice in sheet 3 base on the list in sheet 2 with volume from sheet 1. So expected result is 165 from the sum of ship2, ship7, ship4 and ship3 in sheet 1.


The challenge is no manipulations are allowed in sheet 1 and 2. So i cant use vlookup in sheet 2 and then sum them up. This is because I am retrieving the data from a source database. So once I press refresh sheet 1 and 2 will revert back to default with new entries.

The formula has to be in sheet 3.

Any ideas? Seems like I need some kind of CSE formula ?

Thank you :biggrin:
 
Last edited:

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,169
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
try this

<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 /><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><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Delivered Ship</td><td style=";">Volume</td><td style="text-align: right;;"></td><td style=";">Invoiced Ship</td><td style="text-align: right;;"></td><td style=";">Total Invoice:</td><td style="text-align: right;background-color: #E2EFDA;;">165</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Ship1</td><td style="text-align: right;;">20</td><td style="text-align: right;;"></td><td style=";">Ship2</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;">3</td><td style=";">Ship2</td><td style="text-align: right;;">70</td><td style="text-align: right;;"></td><td style=";">Ship7</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=";">Ship3</td><td style="text-align: right;;">15</td><td style="text-align: right;;"></td><td style=";">Ship4</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;">5</td><td style=";">Ship4</td><td style="text-align: right;;">30</td><td style="text-align: right;;"></td><td style=";">Ship3</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;">6</td><td style=";">Ship5</td><td style="text-align: right;;">20</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;">7</td><td style=";">Ship6</td><td style="text-align: right;;">0</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;">8</td><td style=";">Ship7</td><td style="text-align: right;;">50</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;">9</td><td style=";">Ship8</td><td style="text-align: right;;">15</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;">10</td><td style=";">Ship9</td><td style="text-align: right;;">60</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;">11</td><td style=";">Ship10</td><td style="text-align: right;;">5</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></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)">G1</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">(<font color="Green">A2:A11=D2</font>)+(<font color="Green">A2:A11=D3</font>)+(<font color="Green">A2:A11=D4</font>)+(<font color="Green">A2:A11=D5</font>)</font>),B2:B11</font>)</td></tr></tbody></table></td></tr></table><br />
 

Kingkoopa

Board Regular
Joined
Aug 7, 2014
Messages
94
Hi Alan,

Thanks for the reply! Unfortunately in sheet 2 there are afew thousand lines :(. And the data gets updated daily so the number of data isn't fixed.

Just realise I can't edit my first post anymore. Apologies I just realise the problem is more complicated then before.

There is more than 1 criteria.

sheet 1
CountryDelivered ShipVolume
ChinaShip120
AustraliaShip280
ChinaShip315
GermanyShip430
RussiaShip520
RussiaShip60
USAShip750
USAShip815
AustraliaShip960
AustraliaShip105

<tbody>
</tbody>


sheet 2
CountryInvoice Ship
AustraliaShip2
AustraliaShip10
GermanyShip4
ChinaShip3

<tbody>
</tbody>

sheet 3
CountryTotal Invoice
australia145

<tbody>
</tbody>

Currently I am trying to use a sumifs formula however I am stuck at the 2nd criteria. Below is the formula I have right now : =SUMIFS(Sheet1!C:C,Sheet2!A:A,Sheet3!A2,Sheet1!B:B,Sheet2!B:B)
 
Last edited:

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,169
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
sheet 2
CountryInvoice Ship
AustraliaShip2
AustraliaShip10
GermanyShip4
ChinaShip3

<tbody>
</tbody>

sheet 3
CountryTotal Invoice
australia145

<tbody>
</tbody>

Currently I am trying to use a sumifs formula however I am stuck at the 2nd criteria. Below is the formula I have right now : =SUMIFS(Sheet1!C:C,Sheet2!A:A,Sheet3!A2,Sheet1!B:B,Sheet2!B:B)

shouldn't that be 85?
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,169
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
this is what I came up with, H2:I5 are direct copy from sheet2

<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 /><col /><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><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Country</td><td style=";">Delivered Ship</td><td style=";">Volume</td><td style="text-align: right;;"></td><td style=";">Country</td><td style=";">Invoice Ship</td><td style="text-align: right;;"></td><td style=";">Country</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Total Invoice</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">China</td><td style=";">Ship1</td><td style="text-align: right;;">20</td><td style="text-align: right;;"></td><td style=";">Australia</td><td style=";">Ship2</td><td style="text-align: right;;"></td><td style="background-color: #FFF2CC;;">Australia</td><td style="background-color: #FFF2CC;;">Ship2</td><td style="text-align: right;background-color: #E2EFDA;;">80</td><td style="text-align: right;background-color: #E2EFDA;;">85</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Australia</td><td style=";">Ship2</td><td style="text-align: right;;">80</td><td style="text-align: right;;"></td><td style=";">Australia</td><td style=";">Ship10</td><td style="text-align: right;;"></td><td style="background-color: #FFF2CC;;">Australia</td><td style="background-color: #FFF2CC;;">Ship10</td><td style="text-align: right;;">5</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">China</td><td style=";">Ship3</td><td style="text-align: right;;">15</td><td style="text-align: right;;"></td><td style=";">Germany</td><td style=";">Ship4</td><td style="text-align: right;;"></td><td style="background-color: #FFF2CC;;">Germany</td><td style="background-color: #FFF2CC;;">Ship4</td><td style="text-align: right;;">30</td><td style="text-align: right;;">30</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Germany</td><td style=";">Ship4</td><td style="text-align: right;;">30</td><td style="text-align: right;;"></td><td style=";">China</td><td style=";">Ship3</td><td style="text-align: right;;"></td><td style="background-color: #FFF2CC;;">China</td><td style="background-color: #FFF2CC;;">Ship3</td><td style="text-align: right;;">15</td><td style="text-align: right;;">15</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Russia</td><td style=";">Ship5</td><td style="text-align: right;;">20</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><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;">7</td><td style=";">Russia</td><td style=";">Ship6</td><td style="text-align: right;;">0</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><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;">8</td><td style=";">USA</td><td style=";">Ship7</td><td style="text-align: right;;">50</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><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;">9</td><td style=";">USA</td><td style=";">Ship8</td><td style="text-align: right;;">15</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><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;">10</td><td style=";">Australia</td><td style=";">Ship9</td><td style="text-align: right;;">60</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><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;">11</td><td style=";">Australia</td><td style=";">Ship10</td><td style="text-align: right;;">5</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><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)">Sheet2</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)">J2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$2:$A$11&$B$2:$B$11=H2&I2</font>),$C$2:$C$11</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">K2</th><td style="text-align:left">=IF(<font color="Blue">COUNTIF(<font color="Red">H2:$H$2,H2</font>)=1,SUMIF(<font color="Red">$H$2:$H$5,H2,$J$2:$J$5</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,108,969
Messages
5,525,961
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top