Setting sum values based on another cell value

JocelynF

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!

Michelle Choate 2

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 2

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.