Incorporate SUM by cell color in a formula with HSTACK, MAP, TEXTJOIN and LAMBDA.

[Lixxy]

Hi,

I have two tables, A and B. Table A is countries planned and Table B is planed driven KM.
I also have a summary where I want to see total planed KM, per country, and what calendar weeks that specific country is planed.

I have a formula that summarizes planed driven km in a set of specific countries (B3).
I have a second formula that uses TEXTJOIN to list the calendar weeks each country is planned (C3).

So far so good. But now comes my issue...
Table A and Table B contains plans for two different purposes, differentiated by different colored cells (manually applied) in both tables. Currently both formulas does a sum total, regardless of color.
For the summary to be useful, it needs to be able to summarize one color at a time, preferably by a referenced cell.

If we take DK for example, and the result I would like to get:
DK: 500 KM (CW14) -- Blue cells
DK: 200 KM (CW12; CW13) -- Green cells

I have tried to look into VBA and a function to sum by color, but how ever I do it I cannot get it to work with the rest of the formula.

Can this be done? with VBA?
Or are they any other smart solutions to this problem?

SampleKMCW.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Summary					Table A	CW12	CW13	CW14	CW15
2	Countries	KM	CW			Car 1	transport	DK	DK-DE	DE
3	SE	650	CW12; CW15
4	DK	700	CW12; CW13; CW14			Car2	SE	FI	EE	LV
5	FI	300	CW13
6	DE	500	CW12; CW13; CW14; CW15			Car3	SE-DK-DE	DE	DK	SE
7	LV	150	CW15
8	EE	250	CW14			Table B	CW12	CW13	CW14	CW15
9						Car 1	transport	150	200	150
10
11						Car2	150	300	250	150
12	VAL
13	DEV					Car3	150	200	400	450
Sheet1

Cell Formulas
Range	Formula
B3:B8	B3	=HSTACK(MAP(UNIQUE(TOCOL(TEXTSPLIT(A3:A8,"-"))),LAMBDA(z,SUMPRODUCT(N(ISNUMBER($G$9:$J$13)),$G$9:$J$13,N(ISNUMBER(FIND(z,$G$2:$J$6)))/MAP($G$2:$J$6,LAMBDA(x,COUNTA(TEXTSPLIT(x,"-"))))))))
C3:C8	C3	=IFERROR(MAP(A3:A8,LAMBDA(x,TEXTJOIN("; ",,UNIQUE(TOCOL(IFS(FIND(x,$G$2:$J$6),$G$1:$J$1),3,1))))),"")
Dynamic array formulas.

 

[Lixxy]

How about this then,

I create a third table (C), which is populated with GET.CELL to get the cell color from Table A.
I have another GET.CELL next to the color reference (VAL).

Is it now possible to incorporate into the existing formula that only the cells "37" from Table C is to be used?

The named ranges with Get.Cell i've defined are:
SumColor (N2) --> =GET.CELL(38;Sheet1!G2)
SumColorRef (C12) --> =GET.CELL(38;!B12)

SampleKMCW.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Summary					Table A	CW12	CW13	CW14	CW15			Table C	CW12	CW13	CW14	CW15
2	Countries	KM	CW			Car 1	transport	DK	DK-DE	DE			Car 1	0	36	37	37
3	SE	650	CW12; CW15
4	DK	700	CW12; CW13; CW14			Car2	SE	FI	EE	LV			Car2	37	37	37	37
5	FI	300	CW13
6	DE	500	CW12; CW13; CW14; CW15			Car3	SE-DK-DE	DE	DK	SE			Car3	36	37	37	37
7	LV	150	CW15
8	EE	250	CW14			Table B	CW12	CW13	CW14	CW15
9						Car 1	transport	150	200	150
10
11						Car2	150	300	250	150
12	VAL		37
13	DEV		36			Car3	150	200	400	450
Sheet1

Cell Formulas
Range	Formula
N2:Q2,N6:Q6,N4:Q4	N2	=SumColor
B3:B8	B3	=HSTACK(MAP(UNIQUE(TOCOL(TEXTSPLIT(A3:A8,"-"))),LAMBDA(z,SUMPRODUCT(N(ISNUMBER(Milestones)),Milestones,N(ISNUMBER(FIND(z,DraftPlan)))/MAP(DraftPlan,LAMBDA(x,COUNTA(TEXTSPLIT(x,"-"))))))))
C3:C8	C3	=IFERROR(MAP(A3:A8,LAMBDA(x,TEXTJOIN("; ",,UNIQUE(TOCOL(IFS(FIND(x,DraftPlan),DraftPlanCW),3,1))))),"")
C12:C13	C12	=SumColorRef
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
DraftPlan	=Sheet1!$G$2:$J$6	B3:C3, N2
DraftPlanCW	=Sheet1!$G$1:$J$1	C3
Milestones	=Sheet1!$G$9:$J$13	B3

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Incorporate SUM by cell color in a formula with HSTACK, MAP and LAMBDA. [SOLVED]
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Lixxy]

My apologies,

This is a cross-post and those can be found here, and the solution presented in both:

Incorporate SUM by cell color in a formula with HSTACK, MAP and LAMBDA. [SOLVED]

Hi, I am purely guessing that the solution will need a VBA script, hence me posting here. I have a formula that summarizes planed driven km in a set of specific countries. I have a second formula that uses TEXTJOIN to list the calendar weeks each country is planned. To get an idea...

www.excelforum.com

Incorporate SUM by cell color, together with HSTACK, MAP, TEXTJOIN and LAMBDA

I have two tables where Table A is countries and Table B is driven km. In B3 and C3 I have formulas I have had help creating to summarize driven km and what CW for each specific country. B3: =HSTA...

stackoverflow.com

This issue has been Solved.

Thank you