Vlookup and Countif Across Rows with Criteria

[dhubz]

Hi Everyone,

I can't seem to be able to get this to work. Hoping someone here can shed some light on my issue. On Sheet1! is a Table of information, that looks a lot like the sample. Column A is a unique ID Number(approx 250 rows), Column B, C, D, etc to AJ is an item number. Sheet2! is a summary sheet.

Sheet1!

1877	1.03		1.03	1.02
1433	1.05	1.05	1.03	1.04
625	1.04	1.04	1.04	1.05	1.05
858		1.01	1.02
331	1.03	1.03	1.03	1.05	1.05

<tbody>
</tbody>

Sheet2!

On Sheet2! Col A is a partial list(dynamic) based on a vlookup formula for other criteria.(this part works fine). I need to count
the number of each times the item occurs across the row based on the ID Number. Below is what I am trying to achieve.

ID Number	1.03	1.04	1.05
1433	1	1	2
625		3	2
331	3		2

<tbody>
</tbody>

This is as close as I got, but it doesn't isolate the rows, I've been trying to add a vlookup into the below formula to isolate the rows, but nothing is working.

=IF(NOT(ISERROR(MATCH(A4,Table1[ID '#]))),COUNTIF(SHEET1!2:10,F2),"No Match Found")

Thanks
D

 

[AhoyNC]

Try:
Copy formula down and across as needed. Change ranges to match your data.

Excel Workbook
	A	B	C	D
1	ID Number	1.03	1.04	1.05
2	1433	1	1	2
3	625	0	3	2
4	331	3	0	2
Sheet2

Excel Workbook
	A	B	C	D	E	F
1	1877	1.03		1.03	1.02
2	1433	1.05	1.05	1.03	1.04
3	625	1.04	1.04	1.04	1.05	1.05
4	858		1.01	1.02
5	331	1.03	1.03	1.03	1.05	1.05
Sheet1

 

[dhubz]

Thanks for the quick reply!, Works Great