'Find a value in a column from an array
Hoping someone can help, not even sure how to phrase the question! We have started something of a Harry Potter challenge system at our school. We've divided the pupils into houses and will be awarding points just like in the books. I have managed to dynamically rank the pupils when points are awarded but I need a dynamic overall tally/ranking for each house. So I need a formula that will search the pupil list, find out which house they're in and then add their points to each house total. I'm something of an enthusiastic amateur when it comes to Excel but this has stumped me.
Solution 1:[1]
If you put them into a table with their name, house, and points, you could have a "house rank" by this formula:
=SUM( ($B$2:$B$27=B2)*($C$2:$C$27>C2) ) + 1
putting the formula in this example in E2 (enter it with CTRL+SHIFT+ENTER if you have an older version of Excel) and then copying it down for each pupil.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | mark fitzpatrick |