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 |
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 |
2 Answers
Reset to default 1With the data and formula as presented here, you have two problems. The first problem is that the Criteria Range and Sum Range are different sizes, so Excel doesn't know what to do with them. It's designed to look through a column and find matches, then sum up the single values where it finds matches. You can't use it to sum a list of cells like this - you'd need a separate SumIf statement for each column, thus:
=Sum(SumIf($A$5:A$A33,A37,$C$5:$C$33),SumIf($A$5:A$A33,A37,$C$5:$C$33) [...]SumIf($A$5:A$A33,A37,$C$5:$C$33))
This really isn't a use case for SumIf. With the data as we see it here, you don't need to use SumIf at all - a simple SUM will do the job just fine:
=SUM($C5:$X5)
The $ symbols tell Excel that this part of an address is absolute - it won't be changed when the formula is copied elsewhere. By placing them in front of the column but not the row, it means that when you copy the formula down the row reference will update appropriately. Copy the formula to row 6 and it'll become =SUM($C6:$X6) and so on.
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/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759
=SUMPRODUCT((A$5:A$33=$A5)*$C$5:$X$33)? – P.b Commented 18 hours ago