FILTER takes one include argument, so a rule with two conditions has to be folded into it. Multiplying the two TRUE/FALSE arrays does that row by row, where AND would collapse the whole range into a single answer.
You run accounts receivable for a commercial cleaning company. The finance manager wants this afternoon's collections calls limited to invoices that are worth chasing by phone: $1,000 or more, and more than 30 days overdue. Both have to hold. In F2, use one FILTER formula to list the invoice number, customer and amount (A:C) of every invoice in rows 2 to 9 that meets both conditions.
Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Invoice | Customer | Amount ($) | Days Overdue | Invoice | Customer | Amount ($) | |
| 2 | INV-7701 | Harbor Dental | 2450 | 45 | ||||
| 3 | INV-7702 | Oakridge Cafe | 380 | 62 | ||||
| 4 | INV-7703 | Pinecrest Realty | 1800 | 12 | ||||
| 5 | INV-7704 | Summit Fitness | 5200 | 31 | ||||
| 6 | INV-7705 | Bluewater Marine | 950 | 90 | ||||
| 7 | INV-7706 | Lakeside Vet Clinic | 1000 | 30 | ||||
| 8 | INV-7707 | Copperleaf Studio | 3100 | 0 | ||||
| 9 | INV-7708 | Northgate Logistics | 1275 | 58 |
(C2:C9>=1000)*(D2:D9>30) produces eight answers, one for each invoice, because each comparison produces eight TRUE or FALSE values and multiplying them pairs them up row by row. A TRUE times a TRUE is 1, and anything times a FALSE is 0, so the product is the AND of the two conditions, kept separate for every row. FILTER then keeps the rows marked 1. AND(C2:C9>=1000,D2:D9>30) cannot do this: it gathers all sixteen comparisons into one verdict for the whole table, which is FALSE here, so FILTER would get nothing usable to work with. The data contains both edge cases a careless rule gets wrong. Lakeside Vet Clinic owes exactly $1,000, which passes ">=1000", but it is exactly 30 days overdue, and ">30" means a 30-day invoice is not yet in scope, so it stays off the list. Summit Fitness, at 31 days, is the first day in. Bluewater Marine is 90 days late but under $1,000, so it fails the amount test; that is the right answer for a phone list, but a + instead of the * would have added it back, because adding the arrays means either condition is enough, which is OR.