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

google sheets

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

Best Answer

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)

Related Topic