Sum all items belonging to a category

[LemonMan]

Hi, I am trying to sum up the amounts belonging to certain categories through their ID number but cannot think of any way to find all matching items - xlookup only finds the first item and I can't have a spilling function like filter. The ID number/amount table and ID No./category table must remain separate table. My current function only finds the first result and does not sum all options. I added some sample data (not actual tables or amounts I'm working with) to try to illustrate my issue:

ID No.	amount
1245​	23​
2345​	45​
3453​	56​
6546​	78​
7454​	89​
6335​	87​
7456​	98​
ID No.	category
1245​	red
2345​	red
3453​	green
6546​	blue
7454​	orange
6335​	red
7456​	blue
category	total amount
red	=SUMIF($A$19:$A$25,XLOOKUP(A38,$B$29:$B$35,$A$29:$A$35),$B$19:$B$25)
green	=SUMIF($A$19:$A$25,XLOOKUP(A39,$B$29:$B$35,$A$29:$A$35),$B$19:$B$25)
blue	=SUMIF($A$19:$A$25,XLOOKUP(A40,$B$29:$B$35,$A$29:$A$35),$B$19:$B$25)
orange	=SUMIF($A$19:$A$25,XLOOKUP(A41,$B$29:$B$35,$A$29:$A$35),$B$19:$B$25)

Red should equal 155 but the formula only shows 23 (the first result) and so on.

Any help would be greatly appreciated.

 

[kweaver]

How about:

Book1
	A	B	C	D	E	F	G	H
1	ID No.	amount		ID No.	category		Category	Total
2	1245	23		1245	red		red	155
3	2345	45		2345	red		green	56
4	3453	56		3453	green		blue	176
5	6546	78		6546	blue		orange	89
6	7454	89		7454	orange
7	6335	87		6335	red
8	7456	98		7456	blue
Sheet12

Cell Formulas
Range	Formula
G2:G5	G2	=UNIQUE(E2:E8)
H2:H5	H2	=SUM(XLOOKUP(FILTER($D$2:$D$8,G2=$E$2:$E$8),$A$2:$A$8,$B$2:$B$8))
Dynamic array formulas.

 

[alansidman]

An alternative solution with Power Query

Book5
	A	B	C	D	E	F	G	H
1	ID No.	amount		ID No.	category		category	Sum
2	1245	23		1245	red		red	155
3	2345	45		2345	red		green	56
4	3453	56		3453	green		blue	176
5	6546	78		6546	blue		orange	89
6	7454	89		7454	orange
7	6335	87		6335	red
8	7456	98		7456	blue
Sheet1

Load your two tables to Power Query and then Join them as shown below

let
    Source = Table.NestedJoin(Table1, {"ID No."}, Table2, {"ID No."}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"category"}, {"category"}),
    #"Grouped Rows" = Table.Group(#"Expanded Table2", {"category"}, {{"Sum", each List.Sum([amount]), type nullable number}})
in
    #"Grouped Rows"

 

[Peter_SSs]

Other options are to do them all at once like in column H (no need to copy down) or as in column I (need to copy down)

23 05 04.xlsm
	A	B	C	D	E	F	G	H	I
1	ID No.	amount		ID No.	category		Category	Total
2	1245	23		1245	red		red	155	155
3	2345	45		2345	red		green	56	56
4	3453	56		3453	green		blue	176	176
5	6546	78		6546	blue		orange	89	89
6	7454	89		7454	orange
7	6335	87		6335	red
8	7456	98		7456	blue
Sum Categories

Cell Formulas
Range	Formula
G2:G5	G2	=UNIQUE(E2:E8)
H2:H5	H2	=BYROW(G2#,LAMBDA(rw,SUM(FILTER(B$2:B$8,VLOOKUP(A$2:A$8,D$2:E$8,2,0)=rw))))
I2:I5	I2	=SUM(FILTER(B$2:B$8,VLOOKUP(A$2:A$8,D$2:E$8,2,0)=G2))
Dynamic array formulas.

 

[LemonMan]

How about:

Book1
	A	B	C	D	E	F	G	H
1	ID No.	amount		ID No.	category		Category	Total
2	1245	23		1245	red		red	155
3	2345	45		2345	red		green	56
4	3453	56		3453	green		blue	176
5	6546	78		6546	blue		orange	89
6	7454	89		7454	orange
7	6335	87		6335	red
8	7456	98		7456	blue
Sheet12

Cell Formulas
Range	Formula
G2:G5	G2	=UNIQUE(E2:E8)
H2:H5	H2	=SUM(XLOOKUP(FILTER($D$2:$D$8,G2=$E$2:$E$8),$A$2:$A$8,$B$2:$B$8))
Dynamic array formulas.

This is exactly what I was looking for! Very much appreciated kweaver