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
In FILTER, * is for AND and + is for OR – many students use them the wrong way round, or use the AND() function, which doesn't work on arrays. Write it as (cond1)*(cond2), with each condition in brackets. And give if_empty, otherwise an empty result shows #CALC!.
Ravindra Bagale's Tip – मराठी
FILTER मध्ये AND साठी * आणि OR साठी + – हे बरेच students उलटे वापरतात किंवा AND() function वापरतात, जो array वर चालत नाही. (cond1)*(cond2) असंच लिहा, प्रत्येक condition bracket मध्ये. आणि if_empty द्या, नाहीतर रिकाम्या result ला #CALC! दिसतो.
Ravindra Bagale's Tip – हिंदी
FILTER में AND के लिए * और OR के लिए + – बहुत से students इन्हें उल्टा इस्तेमाल करते हैं या AND() function लगा देते हैं, जो array पर नहीं चलता. (cond1)*(cond2) ऐसे ही लिखो, हर condition bracket में. और if_empty दो, वरना खाली result पर #CALC! दिखता है.
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.