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"


  • In cell N2, enter this formula:

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

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



  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.