Loading exercise…
Loading exercise…
Exercises are built for a keyboard. Read the task here, then open this page on a laptop to solve it.
Dates in criteria. A date criterion is text: an operator joined to a date with &. ">="&B1 means on or after the date in B1. Two pairs on the same date column make a period. Keeping the dates in cells makes the period easy to change.
=COUNTIFS(A2:A90,">="&F1,A2:A90,"<="&F2) tickets opened between F1 and F2
West ran a summer promo in July. Daniel Ortiz wants the completed revenue booked between the promo’s start and end dates, which marketing put in H1 and H2.
In H4, add up the values (D2:D22) of "completed" orders dated from H1 to H2, both days included. Point at H1 and H2 rather than typing the dates.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Order date | Order | Status | Value | Promo start | 2025-07-07 | ||
| 2 | 2025-07-01 | 50547 | completed | $180.40 | Promo end | 2025-07-25 | ||
| 3 | 2025-07-03 | 50552 | completed | $195.75 | ||||
| 4 | 2025-07-03 | 50554 | completed | $84.00 | Promo revenue | |||
| 5 | 2025-07-07 | 50565 | completed | $252.00 | ||||
| 6 | 2025-07-09 | 50573 | completed | $513.00 | ||||
| 7 | 2025-07-10 | 50576 | completed | $195.63 | ||||
| 8 | 2025-07-10 | 50577 | completed | $123.50 | ||||
| 9 | 2025-07-11 | 50582 | completed | $96.93 | ||||
| 10 | 2025-07-14 | 50584 | completed | $92.40 | ||||
| 11 | 2025-07-14 | 50585 | completed | $39.73 | ||||
| 12 | 2025-07-14 | 50587 | completed | $286.50 | ||||
| 13 | 2025-07-15 | 50588 | completed | $271.58 | ||||
| 14 | 2025-07-15 | 50590 | completed | $76.50 | ||||
| 15 | 2025-07-18 | 50603 | completed | $207.00 | ||||
| 16 | 2025-07-21 | 50608 | completed | $329.20 | ||||
| 17 | 2025-07-23 | 50615 | completed | $22.00 | ||||
| 18 | 2025-07-23 | 50616 | completed | $122.82 | ||||
| 19 | 2025-07-24 | 50620 | refunded | $270.00 | ||||
| 20 | 2025-07-25 | 50624 | completed | $506.18 | ||||
| 21 | 2025-07-29 | 50631 | completed | $153.00 | ||||
| 22 | 2025-07-30 | 50635 | refunded | $620.50 | ||||
| 23 | ||||||||
| 24 | ||||||||
| 25 |
=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