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

In sheet 1, Column A contains systems such as 0-20-A0-P01, 0-20-A0-P02, P03, P04, and so on. In other sheets, Column A in sheet 2 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.

Using VLOOKUP for Counting Values

I need to use a VLOOKUP formula in sheet 1 to find out how many "A" are available with respect to 0-20-A0-P01, and how many "B" are 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 have tried using VLOOKUP and COUNTIF but couldn't achieve the desired result. I would appreciate any suggestions, please.

Alternative to VLOOKUP for Handling Duplicates

VLOOKUP won't help as it will pick the first A, B, or C corresponding value in sheet 2 since you mentioned there are duplicates. Try using a Pivot Table; I guess that will help resolve your problem.

Understanding Your Main Motive

Can you specify what your main motive is? Is it:
- a) To get results, i.e., counting how many A, B, C...
- b) To get results only using VLOOKUP and COUNTIF?

If your motive is option (a), then please find the attached Excel sheet; otherwise, reply back, and I will try to work on option (b). However, I agree with nkumar11 that VLOOKUP will only give the first encounter. You can also try a Pivot Table as suggested by nkumar11.

Let's see if any experts can solve it using VLOOKUP along with a combination of other functions because Excel is a vast area, and any combination could give you a result!

Regards

I found the solution for my query by using the SUMPRODUCT function, which is used to check two arrays in a spreadsheet. With the assistance of one of my queries, I was able to resolve the issue. However, all suggestions are also very valuable to me for future updates in my work process. I would really appreciate it if you could come forward to assist me. Thank you very much for all your help. If I have any queries, I will let you know with clear pictures. Thank you once again.

Regards