excel - How do I get the sum of each row? - Stack Overflow

admin管理员组

文章数量:1344975

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

• excel
• excel-formula

Share Improve this question Follow asked 18 hours ago Daniel MayDaniel May 1111 silver badge11 bronze badge New contributor Daniel May is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct. 1

• 3 Do you mean something like =SUMPRODUCT((A$5:A$33=$A5)*$C$5:$X$33) ? – P.b Commented 18 hours ago

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

With 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

本文标签： excelHow do I get the sum of each rowStack Overflow