I'm trying to practice EXCEL functions with my fantasy baseball team's points. However, when I try to take the sum of each row, I get unexpected results ranging from formula errors to incorrect totals.
I've been trying the SUMIF function most but when I get to the sum range field, what to put that returns the expected row without hardcoding it. I've reformatted the data to make it easier to input and look through but that's not getting me where I want to.
For example, I've been trying to look up and sum Alex Bregman's season total and design the formula in a way that won't need continual updates throughout the season. Currently, the formula is as below, with the respective data below that:
| Batter | Formula | Week 1 | Week 2 | True Total | Formula Total |
|---|---|---|---|---|---|
| Alex Bregman | =SUMIF(A$5:A$33,A37,C5:X5) | 5.1 | -.5 | 4.6 | 5.1 |
| Anthony Santander | =SUMIF(A$5:A$33,A38,C6:X6) | 1 | 5.5 | 6.5 | 13 |
If you're using Excel 365 or Excel 2021 and above, an alternative is to use FILTER, that returns an array (or matrix) that you can then sum. SUM(FILTER($C$5:$X$33, A$5:A$33=A37)).
https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759