# Google Sheets – How to Search Sub-String Within a Range

I have looked for some functions like SEARCH(), MATCH(), FIND() but no one can really search a substring in a range and then it returns the row number, position or at least true/false.

Here is my case, I have a sheet like this:

``````-----------------------------
|     Group        | People |
-----------------------------
| Company - Tech   |   16   |
| Company - Sup    |   3    |
| Company - Assist |   8    |
| Family - Extend  |   12   |
| Family - Closed  |   3    |
-----------------------------
``````

Now I need to do count people whose appropriate to similar groups: `Company` and `Family`.

``````|   Big Group      | Count |
----------------------------
| Company          |   ?   |
| Family           |   ?   |
``````

How do I do it on this Google SpreadSheets

You can use the following:
`=IF(A10<>"",SUMIF(\$A\$2:\$A\$6,"*"&A10&"*",\$B\$2:\$B\$6),"")`

A10 is the reference of "Company" in the Big Group
Write the formula instead of ? under Count
Sumif will test the whole column A (\$A\$2:\$A\$6 change it to correspond to all your Data in A and keep \$) for Company (whatever is written with Company) and sum the corresponding Number in People Column \$B\$2:\$B\$6)
You can drag the formula
Keep the \$ in the references for Absolute references
And change A and B to correspond to your Data (from first to last Row)