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.
From Germany, Munich
Acknowledge(0)
Amend(0)
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.
From Germany, Munich
Acknowledge(0)Amend(0)
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.
From Kuwait, Kuwait
Acknowledge(0)Amend(0)
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.
From Kuwait, Kuwait
Acknowledge(0)Amend(0)
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
From Germany, Munich
Acknowledge(0)Amend(0)
Thanks
From Germany, Munich
Acknowledge(0)Amend(0)CiteHR is an AI-augmented HR knowledge and collaboration platform, enabling HR professionals to solve real-world challenges, validate decisions, and stay ahead through collective intelligence and machine-enhanced guidance. Join Our Platform.
PIVOT TABLE SAMPLE.zip
23_excel funaction and data analysis tool.pdf