The formula
Google SheetsGoogle Sheets
=FILTER(A2:B6,REGEXMATCH(A2:A6,"(?i)refund"))In D2, spills: [Refund - order 1182, -45], [REFUND order 1190, -30]
Excel 365 & Google SheetsExcel
=FILTER(A2:B6,ISNUMBER(SEARCH("refund",A2:A6)))In G2, spills: [Refund - order 1182, -45], [REFUND order 1190, -30]
Total of matching rowsExcelGoogle Sheets
=SUMIFS(B2:B6,A2:A6,"*refund*")In J2 → -75
✓ Tested. All 2 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown. 1 result for formulas tagged Google Sheets was checked with a reference implementation of the documented behavior, because LibreOffice can't run that function.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Description | Amount | ||||||||
| 2 | Stripe payout | 820 | Refund - order 1182 | -45 | Refund - order 1182 | -45 | -75 | |||
| 3 | Refund - order 1182 | -45 | REFUND order 1190 | -30 | REFUND order 1190 | -30 | ||||
| 4 | Shopify payout | 610 | ||||||||
| 5 | REFUND order 1190 | -30 | ||||||||
| 6 | Ads | -120 |
How it works
- REGEXMATCH(A2:A6,"(?i)refund")
- TRUE for each row containing "refund" in any case.
- ISNUMBER(SEARCH(…))
- SEARCH returns a position (number) when found, #VALUE! when not; ISNUMBER turns that into TRUE/FALSE. SEARCH is case-insensitive.
- "*refund*"
- SUMIFS wildcard: * means any characters.
Common errors and fixes
Why does the formula return #N/A (Sheets) / #CALC! (Excel)?
No rows contain the text.
Sheets error "REGEXMATCH only works with text"?
The column has numbers. Use REGEXMATCH(TO_TEXT(A2:A6),…).
Does this work in Google Sheets?
Yes. Use the formulas tagged "Google Sheets" on this page; formulas tagged only "Excel" use Excel-specific syntax.