Struggling with VLOOKUP and COUNTIF? How to Count Specific Values Across Sheets?

Hello Sir/Madam,

In Sheet 1, I have a Column A containing systems such as 0-20-A0-P01, 0-20-A0-P02, P03, P04, and so on. In other Sheets 2, Column A has the same systems with many duplicates. In the same Sheet 2, somewhere Column D or Column E have values like "A", "B", "C" with duplicates.

I need to use a VLOOKUP formula in Sheet 1 to find out how many "A" are available in respect of 0-20-A0-P01, and how many "B" are there with respect to 0-20-A0-P01, and so on. The same applies to other systems like 0-20-A0-P02 to determine the count of A, B, C, and so on.

I tried using VLOOKUP and COUNTIF but couldn't achieve the desired result. I would appreciate any suggestions.

Thank you.

Prakash, it seems like you are trying to work on the wrong formula. If you want to know how many A/B/C are present in 0-20-A0-P01, 0-20-A0-P02, and so on, stay on sheet 2 and insert a pivot table. Select the range of columns containing A, B, C.

Remember to list the system numbers in the "RowLabel" and then move A, B, C into the value table. You will then get results like this:

Rowlabel A Count B Count C Count
0-20-A0-P01 2 1 3
0-20-A0-P02 3 4 1
0-20-A0-P03 1 3 2

Please check the attachment for reference. Let me know if this worked.

Thank you for the information. That's the exact result I want. However, my scenario requires working in a sheet without using a pivot table. I hope the SUMPRODUCT function will help, but I still have no idea. If there is any other function that could work, please let me know.

Thanks