If you like this presentation – show it...

Slide 0

Ways toCount
in Excel
7
Function Tips and Tricks

Slide 1

1
Problem #
How many cells contain numbers?
Count Numbers

Slide 2

1
Function #
Only counts numbers
Text and blank cells are ignored
NOTE: Dates are numbers, so they would be counted too
COUNT

Slide 3

2
Problem #
How many cells contain any type of data?
Count All Data

Slide 4

2
Function #
COUNTA
Counts numbers, text, TRUE/FALSE
Blank cells are ignored
NOTE: Cells that look blank might be counted, if they contain an empty string – ""

Slide 5

3
Problem #
How many cells are empty?
Count Empty Cells

Slide 6

3
Function #
COUNTBLANK
Counts cells that are empty
Cells with data are ignored
NOTE: Cells that look blank might not be counted, if they contain an empty string – ""

Slide 7

4
Problem #
How many orders for “Pen”?
Count Specific Items

Slide 8

4
Function #
COUNTIF
Counts cells that contain exact data
NOTE: Criteria can be typed in the formula, or use reference to another cell

Slide 9

5
Problem #
How many item names contain “Pen”?
Count Partial Items

Slide 10

5
Function #
COUNTIF & *
Wildcard “*” used with criteria
“*” represents any characters
NOTE: Criteria can be typed in the formula, or use reference to another cell

Slide 11

6
Problem #
How many Pen orders for 10 or more?
Multiple Criteria

Slide 12

6
Function #
COUNTIFS
Count based on multiple criteria
NOTE: Criteria can be typed in the formula, or use reference to another cell

Slide 13

7
Problem #
How many numbers in filtered list?
Filtered List

Slide 14

7
Function #
SUBTOTAL
SUBTOTAL(102, Only counts numbers in visible rows
NOTE: Option 2 ignores rows hidden by filter; Option 102 also ignores manually hidden rows

Slide 15

More Excel Functions
Count Functions (get the sample file for this slide show)
Sum 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