Count And Sum Multiple Columns Grouped on Column

[slipstream]

Hi All,

I've done a search on my problem and come up with many of different solutions but not quite what i need, things like Unique, V/H Stack, Groupby, Index, Match, Sumifs, Countifs, Lookups. TBH it's a little overwhelming I've not used Excel for a long time so bit out of the loop of the advancements.

Hopefully somebody can point me in the right direction.


I'm looking at producing a daily materials plan, data will change daily, however the fields are constant.
I have a Product which has a choice of colour inside, and outside
The inside and outside colours are interchangeable so I don't need to know if a colour is for the inside or outside just quantity per product, however the colours are exclusive to the Product

(I've tried installing XL2BB, however restrictions on the works network are preventing it installing)

From the attached table, the results expected would be :-

Prod1

White 5​

Blue 2​

Red 2​

Green 1​

​

Prod2

White 2​

Blue 1​

Green 1​

​

Prod3

Green 1​

White 3​

Hopefully, this makes sense, thanks for your time looking.

Product	IntCol	ExtCol
Prod1	White	White
Prod1	White	Green
Prod1	Blue	Blue
Prod1	Red	Red
Prod2	White	Blue
Prod2	White	Green
Prod3	Green	White
Prod3	White	White
Prod1	White	White

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Peter_SSs]

IF you have the relevant functions, would a layout like this be any use?

slipstream.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Product	IntCol	ExtCol			Blue	Green	Red	White	Total
2	Prod1	White	White		Prod1	2	1	2	5	10
3	Prod1	White	Green		Prod2	1	1		2	4
4	Prod1	Blue	Blue		Prod3		1		3	4
5	Prod1	Red	Red		Total	3	3	2	10	18
6	Prod2	White	Blue
7	Prod2	White	Green
8	Prod3	Green	White
9	Prod3	White	White
10	Prod1	White	White
11
Sheet1

Cell Formulas
Range	Formula
E1:J5	E1	=LET(p,Table1[Product],d,HSTACK(VSTACK(p,p),TOCOL(Table1[[IntCol]:[ExtCol]],,1)),c,TAKE(d,,-1),PIVOTBY(TAKE(d,,1),c,c,COUNTA))
Dynamic array formulas.

 

[slipstream]

Perfect, thank you for the quick response much appreciated.

I can work with that, just out of interest would it be easy enough if needed to switch the rows and columns on the results table, so colours down and products across.

 

[Peter_SSs]

Sure, the easy way now would be to just add a TRANSPOSE to the current results

=LET(p,Table1[Product],d,HSTACK(VSTACK(p,p),TOCOL(Table1[[IntCol]:[ExtCol]],,1)),c,TAKE(d,,-1),TRANSPOSE(PIVOTBY(TAKE(d,,1),c,c,COUNTA)))

slipstream.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Product	IntCol	ExtCol			Prod1	Prod2	Prod3	Total
2	Prod1	White	White		Blue	2	1		3
3	Prod1	White	Green		Green	1	1	1	3
4	Prod1	Blue	Blue		Red	2			2
5	Prod1	Red	Red		White	5	2	3	10
6	Prod2	White	Blue		Total	10	4	4	18
7	Prod2	White	Green
8	Prod3	Green	White
9	Prod3	White	White
10	Prod1	White	White
11
Sheet1

Cell Formulas
Range	Formula
E1:I6	E1	=LET(p,Table1[Product],d,HSTACK(VSTACK(p,p),TOCOL(Table1[[IntCol]:[ExtCol]],,1)),c,TAKE(d,,-1),TRANSPOSE(PIVOTBY(TAKE(d,,1),c,c,COUNTA)))
Dynamic array formulas.

 

[Cubist]

If you want the original format:

Book1
	A	B	C	D	E	F
1	Product	IntCol	ExtCol		Prod1
2	Prod1	White	White		White	5
3	Prod1	White	Green		Blue	2
4	Prod1	Blue	Blue		Red	2
5	Prod1	Red	Red		Green	1
6	Prod2	White	Blue		Prod2
7	Prod2	White	Green		White	2
8	Prod3	Green	White		Blue	1
9	Prod3	White	White		Green	1
10	Prod1	White	White		Prod3
11					White	3
12					Green	1
Sheet1

Cell Formulas
Range	Formula
E1:F12	E1	=IFNA(DROP(REDUCE("",UNIQUE(Table1[Product]),LAMBDA(x,y,VSTACK(x,y,LET(t,TOCOL(FILTER(Table1[[IntCol]:[ExtCol]],Table1[Product]=y)),GROUPBY(t,t,COUNTA,,0,-2))))),1),"")
Dynamic array formulas.

 

[Dave Patton]

A slightly different approach.

T202501a.xlsm
	L	M	N	O	P	Q	R	S	T
1	Product	IntCol	ExtCol			Prod1	Prod2	Prod3	Total
2	Prod1	White	White		Blue	2	1		3
3	Prod1	White	Green		Green	1	1	1	3
4	Prod1	Blue	Blue		Red	2			2
5	Prod1	Red	Red		White	5	2	3	10
6	Prod2	White	Blue		Total	10	4	4	18
7	Prod2	White	Green
8	Prod3	Green	White
9	Prod3	White	White
10	Prod1	White	White
1c

Cell Formulas
Range	Formula
P1:T6	P1	=LET(a,VSTACK(L2:M10,HSTACK(L2:L10,N2:N10)),v,CHOOSECOLS(a,2),TRANSPOSE(PIVOTBY(CHOOSECOLS(a,1),v,v,COUNTA)))
Dynamic array formulas.

 

[slipstream]

Thank you both for your additional methods.

 

[slipstream]

Sorry I'm here again, i'm trying to add a filter to the below, I've added a new column to the table and want to filter depending on criteria.

I've created a filter on it's own and all works fine, =FILTER(Table1,Table1[Finish]="Smooth") however when implementing in to the main formula I'm getting an error.

Results

Prod1	White	White	Smooth
Prod1	Red	Red	Smooth
Prod2	White	Blue	Smooth
Prod2	White	Green	Smooth
Prod1	White	White	Smooth

New Table is :-

Product	IntCol	ExtCol	Finish
Prod1	White	White	Smooth
Prod1	White	Green	Texture
Prod1	Blue	Blue	Texture
Prod1	Red	Red	Smooth
Prod2	White	Blue	Smooth
Prod2	White	Green	Smooth
Prod3	Green	White	Texture
Prod3	White	White	Texture
Prod1	White	White	Smooth

This is Peter's solution which works great.

=LET(p,Table1[Product],d,HSTACK(VSTACK(p,p),TOCOL(Table1[[IntCol]:[ExtCol]],,1)),c,TAKE(d,,-1),PIVOTBY(TAKE(d,,1),c,c,COUNTA))

I'm trying to filter on the Finish column ="Smooth" with this forumula

=LET(p,Table1[Product],filtered,FILTER(Table1,Table1[Finish]="Smooth"),d,HSTACK(VSTACK(p,p),TOCOL(filtered[[IntCol]:[ExtCol]],,1)),c,TAKE(d,,-1),TRANSPOSE(PIVOTBY(TAKE(d,,1),c,c,COUNTA)))



expected result:-

	Blue	Green	Red	White	Total
Prod1			2	4	6
Prod2	1	1		2	4
Total	1	1	2	6	10

however, i'm getting the below

Many thanks once again for your invaluable help.

 

[Dave Patton]

T202501a.xlsm
	A	B	C	D	E	F	G	H	I	J	K
3	Product	IntCol	ExtCol	Finish			Blue	Green	Red	White	Total
4	Prod1	White	White	Smooth		Prod1			2	4	6
5	Prod1	White	Green	Texture		Prod2	1	1		2	4
6	Prod1	Blue	Blue	Texture		Total	1	1	2	6	10
7	Prod1	Red	Red	Smooth
8	Prod2	White	Blue	Smooth
9	Prod2	White	Green	Smooth
10	Prod3	Green	White	Texture
11	Prod3	White	White	Texture
12	Prod1	White	White	Smooth
13
1cc

Cell Formulas
Range	Formula
F3:K6	F3	=LET(z,FILTER(A4:D12,D4:D12="Smooth"),a,VSTACK(HSTACK(CHOOSECOLS(z,1),CHOOSECOLS(z,2)),HSTACK(CHOOSECOLS(z,1),CHOOSECOLS(z,3))),v,CHOOSECOLS(a,2),PIVOTBY(CHOOSECOLS(a,1),v,v,COUNTA))
Dynamic array formulas.