9.2 FILTER
=FILTER(array, include, [if_empty])
| Need | Formula |
|---|---|
| All Pune orders | =FILTER(tblMini, tblMini[City]="Pune") |
| Pune AND Delivered | =FILTER(tblMini, (tblMini[City]="Pune")*(tblMini[Status]="Delivered")) |
| Pune OR Nashik | =FILTER(tblMini, (tblMini[City]="Pune")+(tblMini[City]="Nashik")) |
| Orders > value in cell M1 | =FILTER(tblMini, tblMini[Amount]>M1, "No orders") |
| Only some columns | =FILTER(tblMini[[Order ID]:[City]], tblMini[Mins]>15) |
| Text contains "Fruit" | =FILTER(tblMini, ISNUMBER(SEARCH("Fruit", tblMini[Category]))) |
Worked example. Pune and Delivered returns three rows: BLK-1001 (₹64), AMN-1003 (₹110), BLK-1006 (₹150). If the criteria match nothing and if_empty is missing, FILTER returns #CALC! – always give if_empty.
Ravindra Bagale's Tip
FILTER madhe AND sathi * aani OR sathi + – he khup students ulte vapartat kiwa AND() function vapartat, jo array var chalat nahi. (cond1)*(cond2) asach liha, pratyek condition bracket madhe. Aani if_empty dya, nahitar rikami result la #CALC! disto.
Practice task
Build a mini report: a City drop-down in M1 and a FILTER that shows that city's delivered orders with only Order ID, Date and Amount columns; show "No orders" when empty.