Setting sum values based on another cell value
I am trying to set up the top five rows of the Alice ISD points column to calculate the number of earned points for each category(below the blue line) that are also checked under the Items Being Assessed column so they can be divided by the points possible (which already change based on what is checked.
Using the example in the screenshot, Full Time Positions Filled has two points and the points possible for the category are 6. But if I uncheck it, the points possible for the One Texas ACE Community Status will be 4, and I want the Alice ISD points for the One Texas ACE Community Status to also be four so it will show 100%. The current formula for the status rows =SUM([Alice ISD Points]7:[Alice ISD Points]10) / $[Points Possible]@row = Alice ISD One Tecas ACE Community Status points. I just need to only sum the ones that are also checked. I tried some versions of COUNTIF can't make anything parseable.
I appreciate any ideas or guidance. Thank you!
Best Answer
-
You might consider a TOTAL POINTS % column so you do not need a reference that needs to be updated if more rows are added. Because otherwise you will get a circular reference if you try to do a column formula. Also make the items in the "Monitoring Item" match exactly with what is in the dropdown (e.g. remove the word "Status"). Because then the formulas can be the same for all rows. Otherwise you will have to have them separate.
If you do both of my suggestions would be something like:
=SUMIFS([Alice ISD Points]:[Alice ISD Points],[5 to Drive Category]:[5 to Drive Category], HAS(@cell, [Monitoring Item]@row), [Items Being Assessed]:[Items Being Assessed], 1)/ [Points Possible]@row
If you do not want to make any changes then we can use the same formula with making changes to the parts I bold below:
=SUMIFS([Alice ISD Points]7:[Alice ISD Points]100,[5 to Drive Category]:[5 to Drive Category], HAS(@cell, "One Texas ACE Community"), [Items Being Assessed]:[Items Being Assessed], 1)/ [Points Possible]@row
100 - change to the last cell (I am just taking an estimate of where that might be), and continue to update when new rows are added
"One Texas ACE Community" - Change to whatever you are measuring for the row.
Michelle Choate
michelle.choate@outlook.com
Always happy to walk through any project you need help with! Book time with me here: https://calendly.com/michelle-choate
Answers
-
You might consider a TOTAL POINTS % column so you do not need a reference that needs to be updated if more rows are added. Because otherwise you will get a circular reference if you try to do a column formula. Also make the items in the "Monitoring Item" match exactly with what is in the dropdown (e.g. remove the word "Status"). Because then the formulas can be the same for all rows. Otherwise you will have to have them separate.
If you do both of my suggestions would be something like:
=SUMIFS([Alice ISD Points]:[Alice ISD Points],[5 to Drive Category]:[5 to Drive Category], HAS(@cell, [Monitoring Item]@row), [Items Being Assessed]:[Items Being Assessed], 1)/ [Points Possible]@row
If you do not want to make any changes then we can use the same formula with making changes to the parts I bold below:
=SUMIFS([Alice ISD Points]7:[Alice ISD Points]100,[5 to Drive Category]:[5 to Drive Category], HAS(@cell, "One Texas ACE Community"), [Items Being Assessed]:[Items Being Assessed], 1)/ [Points Possible]@row
100 - change to the last cell (I am just taking an estimate of where that might be), and continue to update when new rows are added
"One Texas ACE Community" - Change to whatever you are measuring for the row.
Michelle Choate
michelle.choate@outlook.com
Always happy to walk through any project you need help with! Book time with me here: https://calendly.com/michelle-choate
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!