Loading exercise…
Loading exercise…
Exercises are built for a keyboard. Read the task here, then open this page on a laptop to solve it.
MAXIFS and MINIFS. MAXIFS returns the largest value among the rows that match, MINIFS the smallest. Like SUMIFS, the range you want the answer from comes first, then pairs of range and criteria.
=MINIFS(D2:D30,A2:A30,"Berlin") cheapest Berlin quote
The regional managers want the range of order sizes in early November: the largest and the smallest completed order in each region.
In G2:G5, find the largest order value (C2:C25) for the region in column F; in H2:H5, the smallest. Write the formulas in row 2 and fill down.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Order | Region | Value | Region | Largest | Smallest | ||
| 2 | 50917 | North | $296.30 | North | ||||
| 3 | 50918 | East | $342.00 | South | ||||
| 4 | 50919 | West | $76.50 | East | ||||
| 5 | 50920 | West | $284.00 | West | ||||
| 6 | 50922 | East | $277.00 | |||||
| 7 | 50923 | South | $547.50 | |||||
| 8 | 50924 | West | $93.00 | |||||
| 9 | 50926 | North | $299.70 | |||||
| 10 | 50927 | West | $356.98 | |||||
| 11 | 50928 | South | $354.00 | |||||
| 12 | 50929 | West | $190.95 | |||||
| 13 | 50930 | West | $145.83 | |||||
| 14 | 50931 | West | $104.50 | |||||
| 15 | 50932 | South | $184.13 | |||||
| 16 | 50933 | West | $82.50 | |||||
| 17 | 50934 | East | $181.55 | |||||
| 18 | 50935 | South | $527.80 | |||||
| 19 | 50936 | West | $172.50 | |||||
| 20 | 50937 | North | $80.00 | |||||
| 21 | 50938 | East | $235.00 | |||||
| 22 | 50939 | East | $108.50 | |||||
| 23 | 50940 | East | $53.46 | |||||
| 24 | 50941 | North | $241.98 | |||||
| 25 | 50942 | East | $72.00 | |||||
| 26 | ||||||||
| 27 | ||||||||
| 28 |
=IF(B2>=1.5,SUM(C2:C9),0)Language
Full plan
Excel ✓ · SQL and Power BI also included
Cancel anytime · 14-day full refund · Secure payment by Stripe