Sum Left based on Cell Color

[alexm3430]

I have data in rows C2 thru P2. One of the cells is highlighted in yellow based on conditional formatting rule. The yellow cell is dynamic. For this exercise assume the yellow cell is I2. I want the formula to sum all cells to the left of I2 so cells C2 trhu H2 would be summed.

 

[awoohaw]

Here is a slightly more cleaned up version of the formula. Not sure if it is any better:

=LET(livetarget, $U9,top,$W$6:$AJ$6,bot,$W$5:$AJ$5,colnbrs,SEQUENCE(1,COLUMNS($W$8:$AJ$8)),
rytd,LET(ytd,LET(r, $W9:$AJ9,acc,SCAN(0,r,LAMBDA(a,b,a+b)),acc),ytd),colfound,SUM((((colnbrs)*(livetarget>bot)*(livetarget<=top))))-1,INDEX(rytd,colfound))

 

[alexm3430]

i asked to ignore the conditional formatting and tell me what you want.
Plus, you've moved columns and rows around from the first table you posted.

SO, your conditional formatting is based on the value of "LIVE"

This is what I think the below worksheet does. NOTE: the column references are matched to the best of my abilitiy given your ambiguous details. My suggestion is to copy this into a fresh worksheet first to make sure you agree with the concept.

The concept is:
Step 1: Get one column before the column where the LIVE value is between the top and bottom values in rows 5 & 6 (if you haven't changed it).
Step 2: Start with the anchor column and sum until 1 column before the column identified in step 1.
Step 3 Sum those values.

This is using some functions in 365 that I am not super proficient with and I'm sure that some folks may come up with more elegant formulas. But, this works for the scenario I stated in the concept. If not, then PLEASE write out explicitly what you want, and provide a sample of data and expectations in a table and not an image.

Book1
	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL
1
2
3
4
5			$0	$20	$25	$30	$35	$40	$45	$50	$55	$65	$75	$85	$100	$500
6			$20	$25	$30	$35	$40	$45	$50	$55	$65	$75	$85	$100	$500	$5,000
7
8	Live	Date	0-20	20-25	25-30	30-35	35-40	40-45	45-50	50-55	55-65	65-75	75-85	85-100	100-500	500-5000
9	$65.15	2024-02-24	16.67%	23.33%	16.67%	5.00%	10.00%	5.00%	3.33%	1.67%	1.67%	1.67%	0.00%	3.33%	3.33%	8.33%		83.34%
10	$39.60	2024-03-24	14.49%	17.39%	24.64%	14.49%	7.25%	4.35%	4.35%	4.35%	5.80%	0.00%	1.45%	1.45%	0.00%	0.00%		71.01%
11	$37.38	2024-04-24	11.11%	15.87%	15.87%	9.52%	4.76%	1.59%	7.94%	7.94%	12.70%	4.76%	3.17%	3.17%	1.59%	0.00%		52.37%
12	$39.25	2024-05-24	0.00%	15.15%	22.73%	18.18%	3.03%	1.52%	1.52%	1.52%	6.06%	12.12%	6.06%	6.06%	6.06%	0.00%		56.06%
13	$59.30	2024-06-24	0.00%	1.52%	9.09%	19.70%	9.09%	6.06%	6.06%	3.03%	0.00%	4.55%	12.12%	12.12%	15.15%	1.52%		54.55%
14	$97.68	2024-07-24	0.00%	0.00%	3.13%	6.25%	15.63%	9.38%	6.25%	4.69%	1.56%	7.81%	6.25%	1.56%	35.94%	1.56%		60.95%
15	$152.08	2024-08-24	0.00%	0.00%	0.00%	1.47%	2.94%	10.29%	5.88%	4.41%	2.94%	5.88%	5.88%	11.76%	41.18%	7.35%		51.45%
16	$66.07	2024-09-24	0.00%	0.00%	4.62%	6.15%	6.15%	6.15%	7.69%	16.92%	13.85%	6.15%	9.23%	7.69%	12.31%	3.08%		61.53%
17	$38.64	2024-10-24	1.56%	6.25%	4.69%	4.69%	9.38%	10.94%	7.81%	9.38%	21.88%	10.94%	3.13%	3.13%	4.69%	0.00%		17.19%
Sheet3

Cell Formulas
Range	Formula
AL9:AL17	AL9	=INDEX(LET(top,$W$6:$AJ$6,bot,$W$5:$AJ$5,rytd,LET(ytd,LET(r, $W9:$AJ9,acc,SCAN(0,r,LAMBDA(a,b,a+b)),acc),ytd),rytd),SUM(SEQUENCE(1,COLUMNS($W$8:$AJ$8))*((($U9>$W$5:$AJ$5)*($U9<=$W$6:$AJ$6))))-1)

That worked. Amazing job and thank you for your patience with me.

 

[alexm3430]

Here is a slightly more cleaned up version of the formula. Not sure if it is any better:

=LET(livetarget, $U9,top,$W$6:$AJ$6,bot,$W$5:$AJ$5,colnbrs,SEQUENCE(1,COLUMNS($W$8:$AJ$8)),
rytd,LET(ytd,LET(r, $W9:$AJ9,acc,SCAN(0,r,LAMBDA(a,b,a+b)),acc),ytd),colfound,SUM((((colnbrs)*(livetarget>bot)*(livetarget<=top))))-1,INDEX(rytd,colfound))

If I wanted to convert to summing the right side of the yellow cell what in the formula represents right between left?

 

[awoohaw]

You're welcome.
I'm not sure how to do that. I I'm not sure you can process arrays from the right or bottom. You may be able to do it by summing the entire row and subracting the result of the last formula (but remove the -1 from the formula).

 

[alexm3430]

You're welcome.
I'm not sure how to do that. I I'm not sure you can process arrays from the right or bottom. You may be able to do it by summing the entire row and subracting the result of the last formula (but remove the -1 from the formula).

Yes, that worked perfect. Thank you so much for your help.