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.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Sum Left based on Cell Color
- Thread starter alexm3430
- Start date
awoohaw
Well-known Member
- Joined
- Mar 23, 2022
- Messages
- 4,568
- Office Version
- 365
- Platform
- Windows
- Web
You can't use cell formatting to create a cell reference.
BUT,
You can use the Conditional Formatting rule you just mentioned. Please share that rule.
Additionally, please update your profile to show which version of excel you use. And, share your workbook as a mini worksheet using the xl2bb add in (link below). If you cannot use the add in, then post your data as a table.
Thanks in advance.
BUT,
You can use the Conditional Formatting rule you just mentioned. Please share that rule.
Additionally, please update your profile to show which version of excel you use. And, share your workbook as a mini worksheet using the xl2bb add in (link below). If you cannot use the add in, then post your data as a table.
Thanks in advance.
Last edited:
Upvote
0
| a | b | c | d | e | f | g | h | i | j | k | l | m | n | o | p |
| Date | Live | 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 |
| Feb-24 | $ 65.15 | 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% |
| Mar-24 | $ 39.60 | 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% |
| Apr-24 | $ 37.38 | 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% |
| May-24 | $ 39.25 | 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% |
| Jun-24 | $ 59.30 | 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% |
| Jul-24 | $ 97.68 | 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% |
| Aug-24 | $ 152.08 | 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% |
| Sep-24 | $ 66.07 | 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% |
| Oct-24 | $ 38.64 | 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% |
Upvote
0
Upvote
0
awoohaw
Well-known Member
- Joined
- Mar 23, 2022
- Messages
- 4,568
- Office Version
- 365
- Platform
- Windows
- Web
In your examples I do not see cells W5 or W6. Describing the rule in text as well may help. Are you unable to use xl2bb?
Also, your table post has columns A thru P, but your formatting applies to W thru AM, and W thru AI, how do you get column I to be yellow?
And what column do you want the sum of the queried range to be in?
Also, your table post has columns A thru P, but your formatting applies to W thru AM, and W thru AI, how do you get column I to be yellow?
And what column do you want the sum of the queried range to be in?
Last edited:
Upvote
0
Apologies for the confusion. I am unable to download XL2BB possibly because its my work desktop and it will not allow me. I attached an image that shows the work better.
Attachments
Upvote
0
awoohaw
Well-known Member
- Joined
- Mar 23, 2022
- Messages
- 4,568
- Office Version
- 365
- Platform
- Windows
- Web
What happened to columns H and I? and everything to the left of I? Have you moved columns? What happened to the column labeled "Live"? How does the image here relate to your initial question?
Ive asked twice before... please provide a written description of what you want to occur? What numbers are being compared?
Forget the conditional formatting for a minute. Do you want to sum the cells in a row to the left a value that has met a certain condition. Which I'm guessing is when the sum of the cells from a fixed cell on the left to the current cell fall in between the two boundaries in rows 5 and 6?
Is that right?
And sending a table of the data in the picture would be a great help. I don't want to recreate you data from scratch.
Ive asked twice before... please provide a written description of what you want to occur? What numbers are being compared?
Forget the conditional formatting for a minute. Do you want to sum the cells in a row to the left a value that has met a certain condition. Which I'm guessing is when the sum of the cells from a fixed cell on the left to the current cell fall in between the two boundaries in rows 5 and 6?
Is that right?
And sending a table of the data in the picture would be a great help. I don't want to recreate you data from scratch.
Upvote
0
I want the formula to find the cell highlighted in yellow. Then sum the values in columns V thru AC since cell AD is the one highlighted in yellow on the first row. The answer would be in cell AJ of the same row.
Upvote
0
awoohaw
Well-known Member
- Joined
- Mar 23, 2022
- Messages
- 4,568
- Office Version
- 365
- Platform
- Windows
- Web
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.
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) |
Upvote
0
