# Google sheet formula on counting categorized data

So I have lists of items. Each items belong to different groups. And then I have data of people who has the items. And I want to count how many items of each group that person has.

For example, I have A, B, C, and D. A and B belongs to group 1. C belongs to group 2. D belongs to group 3.

So I have 2 items of group 1, 1 item of group 2, and 1 item of group 3. And I have 4 items in total.

Here are the example in a sheet for a better understanding.

Please help me because I cant figure out what the exact keyword I should search for nor do I know about advanced formulas since I'm really a newbie at this. Thank you.

• Welcome. There are several ways that might this information might be created. In the short term, a more important aspects is how would you expect that it should be presented. Would you please edit your question (and your spreadsheet) to provide an example of how a successful outcome - you might do this for one or two owners. You can calculate the values manually. – Tedinoz Jul 18 at 0:01

You want to count items by group/category for each row of a list. There are likely to be many ways to solve your question. The following should be considered as one alternative:

This answer combines `countif` with `vlookup` in an `arrayformula`.

• In cell N1, O1, P1 and Q1, enter:
• "Unit 1", Unit 2", "Unit 3" and "Unit 4"

Formula

• In cell N2, enter this formula:
`=COUNTIFS(arrayformula(vlookup(\$B2:\$J2,group_data!\$A\$2:\$B\$27,2,false)),N\$1)`

• copy the formula across to Columns O, P and Q

• copy the formula down for as many rows as their are owners.

SAMPLE

• I'm sorry if the explanation on my question is not clear enough but your answer really answers my question. Thank you so much. – arviona Jul 18 at 15:56