Sum Function
Way
Excel AutoSum Button on the Rinbon's Home tab
Keyboard shortcut:Alt+"="
Type the function mannually
Notes
Arguments:cell references or be typed into the formula
References:a block of cells or a selection of non-contiguous cells
Cell references:active worksheet or on other sheets
Ignore empty cells and text values are treated as zero
Plus sign"+" formula
Notes
Apply to only a few cells
Text values cause Error "#VALUE!"
Delete rows or columns cause Error "#REF!"
Grand total
Select the range of cells and the blank row below the range or the blank column to the right
Click the AutoSum button or Keyboard shortcut Alt+"="
Running total
For Eorksheet List
Absolute reference C$2 as starting point
Relative reference C2 as ending point
For Named Table
Absolute reference C$1 as starting point
Structured table reference [@Amt] as ending point
Avoiding the problem when new rows are added to the excel table
OFFSET
Avoiding when inserting a row above the SUM function the new row not being included in the SUM
SUMIF-Sum cells that match criteria
Syntax
SUMIF(range,criteria,[sum_range])
[sum_range],optional argument,if omitted,values in range are summed
Match criteria exactly
SUMIF(A2:A10,"Pen",B2:B10)
SUMIF(A2:A10,B12,B2:B10)--if cell B12 contained the text "Pen"
Match criteria in a string
SUMIF(A2:A10,"*Pen*",B2:B10)
SUMIF(A2:A10,"*"&B12&"*",B2:B10)--if cell B12 contained the text "Pen"
asterisk"*" wildcard character
Match criteria using operators
Sum rows greater than set amount
SUMIF(B2:B10,">=10",C2:C10)
SUMIF(B2:B10,">="&B12,C2:C10)--if cell B12 contained the number 10
Rolling 12 month total
SUMIF(A$2:A2,">="&DATE(YEAR(A2),MONTH(A2)-11,DAY(A2)),B$2:B2)
SUMIFS-Sum cells that match multiple criteria
Syntax
SUMIFS(sum_range,criteria_range1,criteria1,…)
SUMIFS(D3:D10,B3:B10,"Shipped",C3:C10,">="&10)
Problems with Table References
SUMIFS(Sales_Data[Quantity],Sales_Data[Region],$B5,Sales_Data[Category],C$4)
if drag the fill handle to the right, the formula isn't correct,all the table references shifted one column to the right
Solution
Fill Right
Select the cell with the formula and the cells to the right you want to copy the formula
Press Ctrl+R to fill the formula to the right
Select the cell with the formula
Select all the cells to copy the formula
Press Ctrl+V to paste the formula
SUMIFS- With multiple and/or criteria
Array-entered Formula
Type:=SUM(SUMIFS(D4:D15,B4:B14,G4,C4:C15,G6:G7))
Press Ctrl+Shift+Enter keys to array-enter the formula
SUMPRODUCT-Sum cells match multiple criteria
Syntax
SUMPRODUCT(array1,[array2],…)
Sample
SUMPRODUCT(--(A2:A6>="ACTIVE"),--(B2:B6>=10),--(C2:C6))
Sum top5 numbers in list
Few Top Numbers-SUM+LARGE
SUM(LARGE(A1:A7,｛1，2，3｝))
Many Top Numbers-SUM+LARGE+INDIRECT
SUM(LARGE(A1:A50,ROW(INDIRECT("1:10"))))
this is an array entered formula
Variable Top Numbers
SUM(LARGE(A1:A7,ROW(INDIRECT("1:"&C1))))
Sum Amounts in a Date Range
SUMIFS($B$2:$B$9,$A$2:$A:$9,">="&$D$2,$A$2:$A$9,"<="&$E$2)
SUMIF($A$2:$A$9,">="&$D$2,$B$2:$B$9)-SUMIF($A$2:$A$9,">"&$E$2,$B$2:$B$9)
SUBTOTAL - sum Filtered List
Syntax
SUNTOTAL(function_number,ref1…)
Notes
function_numher specifies how the numbers should be calculated
To sum visible cells in a range where rows have been hidden
AGGREGATE-Sum a Filtered List
Syntax
AGGREGATE(function_number,options,array,[k])
AGGREGATE(function_number,options,ref1,…)
Notes
function_number specifies how the numbers should be calculated
options
0:Ignore nested SUBTOTAL and AGGREGATE functions
1:Ignore hidden rows, nested SUBTOTAL and AGGREGATE functions
2:Ignore error values, nested SUBTOTAL and AGGREGATE functions
3:Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions
7:Ignore hidden rows and error values
Sum Specific Items in Filteted List
Sample
SUMPRODUCT(SUBTOTAL(3,OFFSET(A1:A10,ROW(A1:A10)-MIN(ROW(A1:A10)),,1)),--(A1:A10="PEN"),D1:D10)
SUMPRODUCT(--(REGION=A2),(v Amt))
v Amt：DYnamic named ranged
