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

[Kingkoopa]

Hi Guys,

I am currently on windows 7 and using Excel 2016.

I have 3 sheets with following data :

Sheet 1:

Delivered Ship	Volume
Ship1	20
Ship2	70
Ship3	15
Ship4	30
Ship5	20
Ship6	0
Ship7	50
Ship8	15
Ship9	60
Ship10	5

<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

 

[AlanY]

try this

Book1
	A	B	C	D	E	F	G
1	Delivered Ship	Volume		Invoiced Ship		Total Invoice:	165
2	Ship1	20		Ship2
3	Ship2	70		Ship7
4	Ship3	15		Ship4
5	Ship4	30		Ship3
6	Ship5	20
7	Ship6	0
8	Ship7	50
9	Ship8	15
10	Ship9	60
11	Ship10	5
Sheet1

Cell Formulas
Range	Formula
G1	=SUMPRODUCT(--((A2:A11=D2)+(A2:A11=D3)+(A2:A11=D4)+(A2:A11=D5)),B2:B11)

 

[Kingkoopa]

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
Country	Delivered Ship	Volume
China	Ship1	20
Australia	Ship2	80
China	Ship3	15
Germany	Ship4	30
Russia	Ship5	20
Russia	Ship6	0
USA	Ship7	50
USA	Ship8	15
Australia	Ship9	60
Australia	Ship10	5

<tbody>
</tbody>

sheet 2
Country	Invoice Ship
Australia	Ship2
Australia	Ship10
Germany	Ship4
China	Ship3

<tbody>
</tbody>

sheet 3
Country	Total Invoice
australia	145

<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)

 

[Kingkoopa]

bump

 

[AlanY]

sheet 2
Country	Invoice Ship
Australia	Ship2
Australia	Ship10
Germany	Ship4
China	Ship3

<tbody>
</tbody>

sheet 3
Country	Total Invoice
australia	145

<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]

this is what I came up with, H2:I5 are direct copy from sheet2

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Country	Delivered Ship	Volume		Country	Invoice Ship		Country			Total Invoice
2	China	Ship1	20		Australia	Ship2		Australia	Ship2	80	85
3	Australia	Ship2	80		Australia	Ship10		Australia	Ship10	5
4	China	Ship3	15		Germany	Ship4		Germany	Ship4	30	30
5	Germany	Ship4	30		China	Ship3		China	Ship3	15	15
6	Russia	Ship5	20
7	Russia	Ship6	0
8	USA	Ship7	50
9	USA	Ship8	15
10	Australia	Ship9	60
11	Australia	Ship10	5
Sheet2

Cell Formulas
Range	Formula
J2	=SUMPRODUCT(--($A$2:$A$11&$B$2:$B$11=H2&I2),$C$2:$C$11)
K2	=IF(COUNTIF(H2:$H$2,H2)=1,SUMIF($H$2:$H$5,H2,$J$2:$J$5),"")