Excel SUM Function Complete Guide: AutoSum, Hidden Rows, Criteria, and 3D Sums

The Excel SUM function is fundamental, but hidden rows, errors, and criteria can easily cause incorrect totals. If you need conditional sums, see the SUMIF and COUNTIF guide right away. If lookups are involved, XLOOKUP basics can be a big help.

Quick Fix

=SUM(C3:C12)

=SUBTOTAL(9, C3:C12)

=AGGREGATE(9, 6, C3:C12)

=SUMIFS(Sales[Revenue], Sales[Category], "Shoes")

=SUM(A:Z!C10)

SUM Basics

Press Alt+= to enter AutoSum. In a table (CTRL+T), calculate totals using column names, such as =SUM(Orders[Amount]). Clean up text that looks like numbers with Text to Columns or VALUE().

Practical Examples

Totals for Hidden Rows and Filtered Data

=SUBTOTAL(9, C3:C100)   
=SUBTOTAL(109, C3:C100)  

Sum While Ignoring Errors

=AGGREGATE(9, 6, C3:C12)

Conditional Sums

If you need combinations of multiple criteria, see SUMPRODUCT and FILTER for OR and combined criteria for more patterns.

=SUMIFS(Sales[Revenue], Sales[Date], ">="&DATE(2025,9,1), Sales[Date], "<="&DATE(2025,9,30), Sales[Category], "Shoes")

Dynamic Ranges (Spill)

=SUM(J2#)

Multiple-Worksheet (3D) Sums

=SUM(Jan:Dec!C10)

Checklist & Troubleshooting

• Use SUBTOTAL for totals that account for filters.
• Handle error values with AGGREGATE(9,6,...) or IFERROR.
• Use SUMIFS or FILTER→SUM for conditional sums.
• Clean up text numbers with VALUE or Text to Columns.

Related Articles

• How to Use SUMIF and COUNTIF Correctly
• OR and Combined Criteria with SUMPRODUCT and FILTER
• Lock Ranges with Absolute and Relative References (F4)

Conclusion

Using the Excel SUM function correctly can make half of your reports more reliable. Continue with the related articles above to master criteria, OR logic, and fixed references.