Using Excel online for point tracking

Copper Contributor

I am trying to use excel online to keep track of points (people are sorted into different houses, points will be tracked, Hogwarts style). I have set up excel online to have conditional formatting so that based on name or house name, the row will turn the color of the house. Is there a way to sum the points based on the background color to keep track of points? So that in the below sheet, red/yellow/blue point cells can be summed? When I've tried to look up solutions online, Name formatting comes up, but there is not that option on excel online. 


I'd also like to know if it is possible to format cell based on previous cell. I'd like to link names to the house they are in. So that when "Madeline Spencer" is typed in Resident column, "Tower Grove Park House" appears in House column. 


Screenshot 2024-06-15 at 1.23.48 PM.png

1 Reply


Summing cells based on color is complicated, it requires VBA but that is not available in Excel Online.

If you create a list with names in the first column and houses in the second column, you can use the XLOOKUP function to return the house when you enter a name.

And you can use the SUMIFS function to sum points by house, if the house has been filled in consistently.