| Value: | 4533 | Ratio: | |
| Returned Value: | 100 | ||
| Fund | Amount | ||
| 100 | 4533 | ||
| 100 | 654654 | ||
| 100 | 357 | ||
| 100 | 84768 | ||
| 110 | 64 | ||
| 115 | 34 | ||
| 120 | 6154 | ||
| 200 | 56484 | ||
| 200 | 84984 | ||
| 200 | 6846 | ||
| 400 | 4564 | ||
| 400 | 564987 | ||
| 400 | 6848 | ||
| 500 | 687465 |
<tbody>
</tbody>
I'm trying to sum the amounts based on the fund number (e.g. sum of amounts of all 100-level funds, including 110, 115, 120). Then, I need to calculate a formula so that whenever the user inputs a value that matches with one of the amounts, the formula computes the following ratio --> inputted value: sum of all amounts of similar fund level. For example, if the user inputted 4533 and 4533 matches one of the enlisted amounts, then the formula would do 4533 / sum(a14:a20).
Note: I calculated the "Returned Fund" with =INDEX(A14:A28, MATCH(C10, B14:B28)). I thought this may be a helpful reference when creating a formula.
This is a project given to me at work, and no one else knows much about Excel except me. I've tried to create formulas for the past few hours using a combination of VLOOKUP, INDEX, MATCH, and IF but I still haven't gotten a successful output after multiple failed attempts.
Can someone please guide me in resolving this problem?
