Total Values of Products in a list

[pinkpanther6666]

Good Evening All,

On Sheet 1 In column A and Column B i have the following List

Apples 12
Banana 10
Oranges 23
Pears 9
Apples 21
Oranges 19


On Sheet 2 i want to list the totals for each product where ?? is the total of that product on Sheet 1

Apples ??
Banana ??
Oranges ??
Pears ??


Can anyone help me out please

Many Thanks


Steve

 

[alansidman]

ONe solution is use Power Query

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

Book2
	A	B	C	D	E
1	Column1	Column2		Column1	Total
2	Apples	12		Apples	33
3	Banana	10		Banana	10
4	Oranges	23		Oranges	42
5	Pears	9		Pears	9
6	Apples	21
7	Oranges	19
Sheet1

 

[pinkpanther6666]

Alansidman

Many thanks for your quick reply

I will have a look at this

Many Thanks

Steve

 

[kweaver]

Do you already have the unique names on the 2nd sheet?

Book3 (version 1).xlsb
	A	B	C	D	E
1	Apples	12		Apples	33
2	Banana	10		Banana	10
3	Oranges	23		Oranges	42
4	Pears	9		Pears	9
5	Apples	21
6	Oranges	19
Sheet3

Cell Formulas
Range	Formula
E1:E4	E1	=SUMPRODUCT((D1=$A$1:$A$6)*$B$1:$B$6)

(what version of Excel do you run?)

 

[pinkpanther6666]

KWeaver

That worked a treat thanks

Yes i have a list of the Products on Sheet 2

Im Using Excel 365 if that helps

Thanks in advance


Steve

 

[kweaver]

With 365 you can make the list on Sheet2 unique values from Sheet1 using =UNIQUE(Sheet1!range)

Book3
	A	B
1	Apples	33
2	Banana	10
3	Oranges	42
4	Pears	9
Sheet2

Cell Formulas
Range	Formula
A1:A4	A1	=UNIQUE(Sheet1!A1:A6)
B1:B4	B1	=SUMPRODUCT((A1=Sheet1!$A$1:$A$6)*(Sheet1!$B$1:$B$6))
Dynamic array formulas.

 

[lrobbo314]

If you have the 'Let' function...

Book1 (version 2).xlsb
	A	B	C	D	E
1	Apples	12		Apples	33
2	Banana	10		Banana	10
3	Oranges	23		Oranges	42
4	Pears	9		Pears	9
5	Apples	21		Total:	94
6	Oranges	19
Sheet16

Cell Formulas
Range	Formula
D1:E5	D1	=LET(a,A1:A6,b,B1:B6,u,UNIQUE(a),s,SUMIF(a,u,b),c,CHOOSE({1,2},u,s),IFERROR(INDEX(c,SEQUENCE(ROWS(u)+1),{1,2}),CHOOSE({1,2},"Total:",SUM(s))))
Dynamic array formulas.

 

[jtakw]

Hi,

Am I missing something?
Why can't we just use SUMIF?

Book1
	A	B
1	Apples	12
2	Banana	10
3	Oranges	23
4	Pears	9
5	Apples	21
6	Oranges	19
7
8
9
10
Sheet1

Book1
	A	B
1	Apples	33
2	Banana	10
3	Oranges	42
4	Pears	9
Sheet2

Cell Formulas
Range	Formula
B1:B4	B1	=SUMIF(Sheet1!A$1:A$10,A1,Sheet1!B$1:B$10)

 

[kweaver]

Multiple ways to skin the cat.

 

[lrobbo314]

If you have the 'Let' function...

Book1 (version 2).xlsb
	A	B	C	D	E
1	Apples	12		Apples	33
2	Banana	10		Banana	10
3	Oranges	23		Oranges	42
4	Pears	9		Pears	9
5	Apples	21		Total:	94
6	Oranges	19
Sheet16

Cell Formulas
Range	Formula
D1:E5	D1	=LET(a,A1:A6,b,B1:B6,u,UNIQUE(a),s,SUMIF(a,u,b),c,CHOOSE({1,2},u,s),IFERROR(INDEX(c,SEQUENCE(ROWS(u)+1),{1,2}),CHOOSE({1,2},"Total:",SUM(s))))
Dynamic array formulas.

I did.

=LET(a,A1:A6,b,B1:B6,u,UNIQUE(a),s,SUMIF(a,u,b),c,CHOOSE({1,2},u,s),IFERROR(INDEX(c,SEQUENCE(ROWS(u)+1),{1,2}),CHOOSE({1,2},"Total:",SUM(s))))