Need a formulae to tabulate data from left table to be populated onto right table seamlessly

Copper Contributor

Hi all, hope you guys are well. Have an important question here!


As you can see in the picture attached, I want to create a formula that could tabulate the number of times a particular label (A2 or C7 or P7, etc) is given to a person within a test (a table). 


Taking Adam as an example, in test #34, Adam has 1 x A2, 4 x C7, 2 x P7, 2 x U5)


Does anyone know what formulae I can input on the right table to easily track these data from the left table and input them instantly on the right table based on the various names (especially if the list on the right side names are not in order on the right)


Sincerely appreciate every single piece of advice!!


Thank you very much!



4 Replies



You can try SUMPRODUCT.



Hi there, I truly appreciate your help but unfortunately it does not work... 

I have attached the file here, truly appreciate your help! @OliverScheurich 


Would actually need the name list on the right side to be longer/more extensive and not the same as the one on the left. Could you assist me in this? Truly appreciate it!!

Here is the link to the draft

Excel Help.xlsx

best response confirmed by Nicholasccy (Copper Contributor)


SUMPRODUCT returns the expected results in your file on my computer.