BYCOL and LAMBDA

[Ashish Mathur]

Hi,
Assume this data is in range A1:M8. Months are in B1:M1 and products are in A2:A8. As you can see, products get repeated in A2:A8.

Product

Sep

Oct

Nov

Dec

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

A

1​

2​

3​

4​

5​

6​

7​

8​

9​

10​

11​

12​

B

2​

3​

4​

5​

6​

7​

8​

9​

10​

11​

12​

13​

C

3​

4​

5​

6​

7​

8​

9​

10​

11​

12​

13​

14​

A

4​

5​

6​

7​

8​

9​

10​

11​

12​

13​

14​

15​

B

5​

6​

7​

8​

9​

10​

11​

12​

13​

14​

15​

16​

D

6​

7​

8​

9​

10​

11​

12​

13​

14​

15​

16​

17​

A

7​

8​

9​

10​

11​

12​

13​

14​

15​

16​

17​

18​

In cell A11, i enter this formula

=UNIQUE(A2:A8)

My objective is to get the monthwise total for each product. So, in cell B11, I enter this formula

=BYCOL($B$2:$M$8,LAMBDA(c,SUMIF($A$2:$A$8,A11,c)))

As expected, this spills column wise perfectly. However, I'd like it to spill row wise as well.

What modification would i need in cell B11 to make the formula spill row wise and column wise?

Thank you.

 

[Sanjeev1976]

Hi Ashish, you can try the following :

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Product	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug
2	A	1	2	3	4	5	6	7	8	9	10	11	12
3	B	2	3	4	5	6	7	8	9	10	11	12	13
4	C	3	4	5	6	7	8	9	10	11	12	13	14
5	A	4	5	6	7	8	9	10	11	12	13	14	15
6	B	5	6	7	8	9	10	11	12	13	14	15	16
7	D	6	7	8	9	10	11	12	13	14	15	16	17
8	A	7	8	9	10	11	12	13	14	15	16	17	18
9
10	A	12	15	18	21	24	27	30	33	36	39	42	45
11	B	7	9	11	13	15	17	19	21	23	25	27	29
12	C	3	4	5	6	7	8	9	10	11	12	13	14
13	D	6	7	8	9	10	11	12	13	14	15	16	17
Sheet1

Cell Formulas
Range	Formula
A10:M13	A10	=LET(a,UNIQUE(A2:A8),HSTACK(a,MMULT(--(TRANSPOSE(A2:A8)=a),B2:M8)))
Dynamic array formulas.

 

[Fluff]

Another option

=DROP(REDUCE("",A11#,LAMBDA(a,b,VSTACK(a,BYCOL($B$2:$M$8,LAMBDA(bc,SUMIF($A$2:$A$8,b,bc)))))),1)

 

[Ashish Mathur]

Hi Ashish, you can try the following :

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Product	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug
2	A	1	2	3	4	5	6	7	8	9	10	11	12
3	B	2	3	4	5	6	7	8	9	10	11	12	13
4	C	3	4	5	6	7	8	9	10	11	12	13	14
5	A	4	5	6	7	8	9	10	11	12	13	14	15
6	B	5	6	7	8	9	10	11	12	13	14	15	16
7	D	6	7	8	9	10	11	12	13	14	15	16	17
8	A	7	8	9	10	11	12	13	14	15	16	17	18
9
10	A	12	15	18	21	24	27	30	33	36	39	42	45
11	B	7	9	11	13	15	17	19	21	23	25	27	29
12	C	3	4	5	6	7	8	9	10	11	12	13	14
13	D	6	7	8	9	10	11	12	13	14	15	16	17
Sheet1

Cell Formulas
Range	Formula
A10:M13	A10	=LET(a,UNIQUE(A2:A8),HSTACK(a,MMULT(--(TRANSPOSE(A2:A8)=a),B2:M8)))
Dynamic array formulas.

Thank you for helping Sanjeev. That works well.

 

[Ashish Mathur]

Another option

=DROP(REDUCE("",A11#,LAMBDA(a,b,VSTACK(a,BYCOL($B$2:$M$8,LAMBDA(bc,SUMIF($A$2:$A$8,b,bc)))))),1)

Thank you for helping Fluff.

 

[Sanjeev1976]

Thank you for the feedback Ashish

 

[Fluff]

Glad we could help & thanks for the feedback.