Solved - How to sum dynamic arrays using FILTER and SEARCH for multiple partial matches

📌 The Problem Explained

Imagine you are managing a large expense report. You have a column for Description and a column for Amount. You need to calculate the total sum of all expenses that contain specific keywords—for example, any item containing “Cloud”, “SaaS”, or “Hosting”.

The challenge is that standard functions like SUMIF do not support multiple partial matches easily, and hard-coding keywords into your formula makes it difficult to update. You need a dynamic array solution that filters your data based on several “contains” criteria and sums the results automatically.

💡 The Quick Solution

Use this formula structure to sum values based on two partial match criteria (OR logic):

=SUM(FILTER(B2:B20, ISNUMBER(SEARCH("Cloud", A2:A20)) + ISNUMBER(SEARCH("SaaS", A2:A20)), 0))

Note: Replace B2:B20 with your values and A2:A20 with your text descriptions.

🛠️ Step-by-Step Breakdown

To implement this solution, follow these steps to build the logic from the inside out.

Step	Action	Description
1	Identify Ranges	Select the Value Range (what to sum) and the Criteria Range (where to search).
2	Apply SEARCH	Use SEARCH("keyword", Range) to find the position of the text. It returns a number if found or an error if not.
3	Wrap in ISNUMBER	Use ISNUMBER() to convert the search results into TRUE or FALSE values.
4	Combine Criteria	Use the + (plus) sign between ISNUMBER blocks. In dynamic arrays, + acts as the OR operator.
5	Apply FILTER	Wrap the logic in FILTER(ValueRange, BooleanLogic) to extract only the matching rows.
6	Execute SUM	Wrap the entire expression in SUM() to get your final total.

If you need to use a range of cells (e.g., D1:D3) as your keyword list rather than typing them manually, use this advanced logic:

=SUM(FILTER(B2:B20, MMULT(--ISNUMBER(SEARCH(TRANSPOSE(D1:D3), A2:A20)), SEQUENCE(ROWS(D1:D3), 1, 1, 0)) > 0))

⚠️ Common Mistakes to Avoid

1. Using the AND operator instead of OR: When using partial matches for multiple keywords in the same column, use the + sign. Using the * (asterisk/AND) sign would require the cell to contain all keywords simultaneously, which usually results in a zero sum.
2. Forgetting ISNUMBER: The SEARCH function returns a #VALUE! error if it doesn’t find the text. Without ISNUMBER, the FILTER function will fail because it cannot process error values in the logic array.
3. Absolute vs Relative References: If you are dragging this formula, ensure your ranges are locked (e.g., $A$2:$A$20) to prevent the formula from breaking.