IntermediateData Analysis
Total only the orders above average
Sum the orders that beat the average, without typing the average in yourself.
SUMIF · AVERAGE
8 min25 points
Count backorders on three conditions
The situation
The Columbus warehouse manager is arguing that their backorder problem is limited to a handful of large lines. To settle it, someone needs the count of Columbus backorders over 100 units — all three conditions together.
A1
fx
| Row number | A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|---|
| 1 | Warehouse | Status | Units | ||||
| 2 | Columbus | Backorder | 240.00 | Large Columbus backorders | |||
| 3 | Reno | Backorder | 180.00 | ||||
| 4 | Columbus | Shipped | 320.00 | ||||
| 5 | Columbus | Backorder | 60.00 | ||||
| 6 | Reno | Shipped | 150.00 | ||||
| 7 | Columbus | Backorder | 410.00 | ||||
| 8 | Atlanta | Backorder | 220.00 | ||||
| 9 | Columbus | Shipped | 90.00 | ||||
| 10 | Reno | Backorder | 130.00 | ||||
| 11 | Columbus | Backorder | 95.00 | ||||
| 12 |
Functions in this exercise
- COUNTIFS — Counts cells that meet several conditions at once.
Who this is for
Recommended next
Related exercises
- IntermediateStatistical Functions
Count orders meeting two conditions
Count rows that satisfy several criteria at once.
COUNTIFS
8 min25 points - IntermediateMath Functions
Total sales for a region and month with SUMIFS
Apply two conditions at once to a conditional sum.
SUMIFS
9 min25 points - IntermediateData Analysis
Count orders inside a value band
Apply an upper and a lower bound to the same column at once.
COUNTIFS
7 min25 points