Unit 5 of 12 Free

Conditional math

Count and sum only what matches.

Key ideas

=COUNTIF(B2:B7,"East")

A range to check, then the condition. Matching ignores upper and lower case.

">=100"

Comparisons go inside quotes. To use a cell value: ">"&F1.

=SUMIFS(C2:C7,B2:B7,"East")

SUMIF has the sum range last. SUMIFS has it first. A classic gotcha.

How many products sell in the East region? Put the count in F2.
A B C D E F
1 Product Region Units Price East count
2 Notebook East 120 3.5
3 Pen West 340 1.25
4 Stapler East 45 12
5 Binder North 80 6.75
6 Marker West 210 2.4
7 Folder North 150 1.9
Hint: =COUNTIF(B2:B7,"East")
Show answer
=COUNTIF(B2:B7,"East") in F2. COUNTIF checks each cell in the range against your condition.
What will B1 show?
A B
1 40
2 75
3 51
4 50
5 n/a
B1 contains =COUNTIF(A1:A5,">50")
Show answer
2. 75 and 51 are greater than 50. 50 itself is not.
Build: count products with 100 or more units
Show answer
=COUNTIF(C2:C7,">=100"). Comparison conditions must be inside quotes.
Why does ">=100" need quotes in COUNTIF?
- The condition is passed as text that COUNTIF reads
- Quotes make it faster
- Numbers always need quotes
- It does not need quotes
Show answer
The condition is passed as text that COUNTIF reads. COUNTIF parses the text to find the operator and the value.
F1 holds a region name. Count how many products are in that region, in F2.
A B C D E F
1 Product Region Units Price Region West
2 Notebook East 120 3.5 Count
3 Pen West 340 1.25
4 Stapler East 45 12
5 Binder North 80 6.75
6 Marker West 210 2.4
7 Folder North 150 1.9
Hint: Use F1 as the condition instead of typing the region.
Show answer
=COUNTIF(B2:B7,F1) in F2. Pointing at an input cell turns your formula into a mini tool.

Total the units sold in the West region, in F2.
A B C D E F
1 Product Region Units Price West units
2 Notebook East 120 3.5
3 Pen West 340 1.25
4 Stapler East 45 12
5 Binder North 80 6.75
6 Marker West 210 2.4
7 Folder North 150 1.9
Hint: =SUMIF(where to check, what to match, what to add)
Show answer
=SUMIF(B2:B7,"West",C2:C7) in F2. Pen (340) plus Marker (210) is 550.
What is the average Score for the Sales department? Put it in F2.
A B C D E F
1 Name Dept Score Hours Sales avg
2 Ava Sales 94 42
3 Ben Support 81 38
4 Cara Sales 88 45
5 Dev Ops 97 36
6 Eva Support 91 41
7 Finn Ops 73 44
Hint: Same shape as SUMIF, just a different function.
Show answer
=AVERAGEIF(B2:B7,"Sales",C2:C7) in F2. Ava (94) and Cara (88) average to 91.
In =SUMIF(B2:B7,"West",C2:C7), what is C2:C7?
- The cells to add up
- The cells to check
- The condition
- The output cell
Show answer
The cells to add up. SUMIF(range, criteria, sum_range). The sum range is last.
Build: total hours for the Ops department
Show answer
=SUMIF(B2:B7,"Ops",D2:D7). Check the Dept column, add the Hours column.
What will C1 show?
A B C
1 Tea 3
2 Coffee 5
3 Tea 4
C1 contains =SUMIF(A1:A3,"Tea",B1:B3)
Show answer
7. Only the Tea rows are added: 3 + 4.

Where does the sum range go in SUMIFS?
- First
- Last
- Second
- Anywhere
Show answer
First. SUMIFS(sum_range, range1, criteria1, ...). Opposite of SUMIF.
Total units for East products priced above $5. Put the answer in F2.
A B C D E F
1 Product Region Units Price East > $5
2 Notebook East 120 3.5
3 Pen West 340 1.25
4 Stapler East 45 12
5 Binder North 80 6.75
6 Marker West 210 2.4
7 Folder North 150 1.9
Hint: =SUMIFS(C2:C7, B2:B7, "East", D2:D7, ">5")
Show answer
=SUMIFS(C2:C7,B2:B7,"East",D2:D7,">5") in F2. Only the Stapler (45 units at $12) matches both.
Count Support team members who scored 85 or higher, in F2.
A B C D E F
1 Name Dept Score Hours Support 85+
2 Ava Sales 94 42
3 Ben Support 81 38
4 Cara Sales 88 45
5 Dev Ops 97 36
6 Eva Support 91 41
7 Finn Ops 73 44
Hint: COUNTIFS takes pairs: range, condition, range, condition.
Show answer
=COUNTIFS(B2:B7,"Support",C2:C7,">=85") in F2. Eva (91) qualifies. Ben scored 81.
Build: count Sales people working more than 40 hours
Show answer
=COUNTIFS(B2:B7,"Sales",D2:D7,">40"). Each condition comes as a range plus its criteria.
What will B1 show?
A B
1 Stapler
2 Sticker
3 Pen
4 Scissors
B1 contains =COUNTIF(A1:A4,"S*")
Show answer
3. The * wildcard matches any characters, so S* matches anything starting with S.