If you like this presentation – show it...

Slide 0

Ways toSum
in Excel
7
Function Tips and Tricks

Slide 1

1
Problem #
How to get a quick Grand Total for Rows and Columns?
Quick Grand Total

Slide 2

1
Function #
Quick Grand Total
Select number cells and theblank total cells
Double-click the AutoSum button on Excel Ribbon

Slide 3

2
Problem #
How to total specific cells in a range?
Sum Specific Cells

Slide 4

2
Function #
SUM formula: Ctrl + Click to select multiple cells
+ operator shows errors if cells contain text
SUM or +

Slide 5

3
Problem #
What is the current balance?
Show Running Total

Slide 6

3
Function #
Running Total
Lock starting row with absolute row reference: C$2
Copy down
Start row locked, end row increases

Slide 7

4
Problem #
How many Binders were ordered?
Sum Specific Items

Slide 8

4
Function #
SUMIF
Sums cells based on specific value in each row
Type criterion in formula, or use cell reference

Slide 9

5
Problem #
How many “Pen” items were ordered?
Sum Partial Match

Slide 10

5
Function #
SUMIF & *
Wildcard “*” used with criterion
“*” = any characters
Type criteria in formula, or use cell reference

Slide 11

6
Problem #
How many cases sold
in Pen orders in month 2 or later?
Multiple Criteria

Slide 12

6
Function #
SUMIFS
Sum based on multiple criteria
Type criteria in formula, or use cell reference

Slide 13

7
Problem #
How many units sold for items in
filtered
list?
Filtered List

Slide 14

7
Function #
SUBTOTAL
SUBTOTAL(109,
Sums visible numbers
Ignores rows
hidden by filter
Or manually hidden
SUBTOTAL(9,
Sums visible numbers
Ignores rows hidden by filter only

Slide 15

More Excel Functions
Sum Functions (get the sample file for this slide show)
Count Functions
VLOOKUP Function
INDEX / MATCH Functions
INDIRECT Function

Slide 16

Excel Tips & Tutorials
http://www.contextures.com/30excelfunctionsin30days01.html
Visit Contextures.com
30 Excel Functions Kit