Tigerexcel
Active Member
- Joined
- Mar 6, 2020
- Messages
- 493
- Office Version
- 365
- 2019
- Platform
- Windows
I'm after a formula that will sum the values above until it reaches a text entry.
[]
A number of people who don't use Excel regularly have to use the spreadsheet and will need to enter/delete items accordingly. To ensure that it calculates properly I'd like the formula to add values above it until it reaches a word, in this case Units
I thought it could be done through OFFSET but couldn't get it to work. B12 should return 38, B6 should return 20 at the moment but the formula needs to be able to add everything from the Total row until it finds Units which will change depending on how many rows are added/deleted for each Subject category.
Book14 | |||||
---|---|---|---|---|---|
B | C | D | |||
1 | Subject | ||||
2 | Jack | Mary | |||
3 | History | Units | Units | ||
4 | 12 | 5 | |||
5 | 8 | 5 | |||
6 | Total | 20 | 10 | ||
7 | Science | Units | Units | ||
8 | 8 | 4 | |||
9 | 10 | 6 | |||
10 | 15 | 8 | |||
11 | 5 | 2 | |||
12 | Total | 38 | 20 | ||
Sheet2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C6:D6 | C6 |
A number of people who don't use Excel regularly have to use the spreadsheet and will need to enter/delete items accordingly. To ensure that it calculates properly I'd like the formula to add values above it until it reaches a word, in this case Units
I thought it could be done through OFFSET but couldn't get it to work. B12 should return 38, B6 should return 20 at the moment but the formula needs to be able to add everything from the Total row until it finds Units which will change depending on how many rows are added/deleted for each Subject category.