Database

[PatrickTT]

If I need to sum the values of each ingredient, and some ingredients are sourced from multiple suppliers, what is the easiest way to do this?

Ingredient	Supplier	Volume
Tomatoes	Johnson & sons	200
Tomatoes	MD supplies	150
Oranges	MD supplies	10
Apples	Tom's Fresh fruit & Veg	20

 

[Joe4]

Please show us what you want the expected output (of your example above) to look like.

 

[PatrickTT]

Ingredient	Volume
Tomatoes	350​
Oranges	10​
Apples	20​

 

[Joe4]

Please don't mark a post as the solution if a solution has not been found yet!

One way would be to copy over the Ingredient column to another range, and then use the "Remove Duplicate" functionality to remove all duplicates.

Then, use a SUMIF formula to get the sum of each ingredient.
See: MS Excel: How to use the SUMIF Function (WS)

 

[alansidman]

An alternative means is with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Volume", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Ingredient"}, {{"Total", each List.Sum([Volume]), type nullable number}})
in
    #"Grouped Rows"

Book8
	A	B	C	D	E	F
1	Ingredient	Supplier	Volume		Ingredient	Total
2	Tomatoes	Johnson & sons	200		Tomatoes	350
3	Tomatoes	MD supplies	150		Oranges	10
4	Oranges	MD supplies	10		Apples	20
5	Apples	Tom's Fresh fruit & Veg	20
Sheet1

 

[PatrickTT]

Please don't mark a post as the solution if a solution has not been found yet!

One way would be to copy over the Ingredient column to another range, and then use the "Remove Duplicate" functionality to remove all duplicates.

Then, use a SUMIF formula to get the sum of each ingredient.
See: MS Excel: How to use the SUMIF Function (WS)

Thanks, this worked perfectly! (PS didn't realise I had marked it as solved...)

 

[Joe4]

You are welcome.
Glad we were able to help!