Loading exercise…
Loading exercise…
Exercises are built for a keyboard. Read the task here, then open this page on a laptop to solve it.
AND inside FILTER. FILTER takes one include argument, so two conditions are joined by multiplying them: (cond1)*(cond2). TRUE counts as 1 and FALSE as 0, so the product is 1 only where both are TRUE. Put each condition in its own brackets. AND() won't work here, because it returns one value for the whole range.
=FILTER(A2:E40,(C2:C40="Cleaning")*(E2:E40>100))
Priya Raman reviews every large East order by hand before the month closes. Her region and her cut-off are in Q1:R2, so she can reuse the sheet next month.
In I2, return every row of A2:G32 whose region (column D) equals R1 and whose value (column G) is at least R2. Point at R1 and R2 rather than typing East or the amount. The headers are in I1:O1.
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Order ID | Order date | Customer | Region | Channel | Status | Value | Order ID | Order date | Customer | Region | Channel | Status | Value | Region | East | ||
| 2 | 51006 | 2025-12-01 | Pioneer Consulting | North | Sales Rep | completed | $371.80 | Minimum value | $250.00 | |||||||||
| 3 | 51007 | 2025-12-01 | Willow Studios | North | Sales Rep | completed | $157.50 | |||||||||||
| 4 | 51008 | 2025-12-01 | Harbor Labs | East | Online | completed | $320.45 | |||||||||||
| 5 | 51009 | 2025-12-01 | Falcon Consulting | North | Sales Rep | completed | $42.00 | |||||||||||
| 6 | 51010 | 2025-12-02 | Beacon Hotels | East | Partner | completed | $275.60 | |||||||||||
| 7 | 51011 | 2025-12-02 | Willow Law Group | East | Online | completed | $224.00 | |||||||||||
| 8 | 51012 | 2025-12-02 | Copper Media | South | Online | completed | $251.55 | |||||||||||
| 9 | 51013 | 2025-12-03 | Willow Studios | North | Sales Rep | completed | $29.70 | |||||||||||
| 10 | 51014 | 2025-12-03 | River Print Co | West | Online | completed | $182.50 | |||||||||||
| 11 | 51015 | 2025-12-03 | Maple Academy | East | Sales Rep | completed | $841.60 | |||||||||||
| 12 | 51016 | 2025-12-03 | Willow Academy | North | Online | completed | $181.00 | |||||||||||
| 13 | 51017 | 2025-12-04 | Orchard Media | West | Online | completed | $74.00 | |||||||||||
| 14 | 51018 | 2025-12-04 | Willow Hotels | West | Sales Rep | completed | $86.40 | |||||||||||
| 15 | 51019 | 2025-12-04 | Copper Media | South | Online | completed | $148.50 | |||||||||||
| 16 | 51020 | 2025-12-04 | Bright Hotels | East | Sales Rep | completed | $118.50 | |||||||||||
| 17 | 51021 | 2025-12-05 | Harbor Fitness | South | Sales Rep | completed | $152.40 | |||||||||||
| 18 | 51022 | 2025-12-05 | Copper Media | South | Online | completed | $175.00 | |||||||||||
| 19 | 51023 | 2025-12-05 | Orchard Clinic | South | Online | cancelled | $823.00 | |||||||||||
| 20 | 51024 | 2025-12-05 | Copper Fitness | South | Sales Rep | completed | $223.00 | |||||||||||
| 21 | 51025 | 2025-12-06 | Pioneer Realty | West | Sales Rep | completed | $195.70 | |||||||||||
| 22 | 51026 | 2025-12-08 | Blue Accounting | North | Online | completed | $92.40 | |||||||||||
| 23 | 51027 | 2025-12-08 | Willow Hotels | West | Sales Rep | completed | $248.47 | |||||||||||
| 24 | 51028 | 2025-12-08 | Beacon Architects | North | Online | completed | $219.60 | |||||||||||
| 25 | 51029 | 2025-12-09 | Orchard Academy | South | Online | completed | $402.55 | |||||||||||
| 26 | 51030 | 2025-12-09 | Copper Media | South | Online | completed | $169.50 | |||||||||||
| 27 | 51031 | 2025-12-09 | Harbor Consulting | North | Online | completed | $747.83 | |||||||||||
| 28 | 51032 | 2025-12-10 | North Architects | South | Partner | completed | $19.80 | |||||||||||
| 29 | 51033 | 2025-12-10 | Cedar Realty | North | Sales Rep | completed | $122.50 | |||||||||||
| 30 | 51034 | 2025-12-10 | Harbor Hotels | West | Partner | completed | $182.00 | |||||||||||
| 31 | 51035 | 2025-12-10 | Harbor Studios | East | Online | refunded | $504.00 | |||||||||||
| 32 | 51036 | 2025-12-10 | Pioneer Realty | West | Online | completed | $105.00 | |||||||||||
| 33 | ||||||||||||||||||
| 34 | ||||||||||||||||||
| 35 |
=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