Array FunctionsAdvanced

Build a Collections Call List with FILTER and Two Conditions

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.

Task:

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.

Interactive Spreadsheet

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

The data in this exercise

This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.

9 rows × 8 columns10 cells you fill in
ABCDEFGH
1InvoiceCustomerAmount ($)Days OverdueInvoiceCustomerAmount ($)
2INV-7701Harbor Dental245045
3INV-7702Oakridge Cafe38062
4INV-7703Pinecrest Realty180012
5INV-7704Summit Fitness520031
6INV-7705Bluewater Marine95090
7INV-7706Lakeside Vet Clinic100030
8INV-7707Copperleaf Studio31000
9INV-7708Northgate Logistics127558
What this exercise teachesMay contain the answer

(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.