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!
[TABLE="class: grid, width: 800, align: center"]
<tbody>[TR]
[TD]1877[/TD]
[TD]1.03[/TD]
[TD][/TD]
[TD]1.03[/TD]
[TD]1.02[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1433[/TD]
[TD]1.05[/TD]
[TD]1.05[/TD]
[TD]1.03[/TD]
[TD]1.04[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]625[/TD]
[TD]1.04[/TD]
[TD]1.04[/TD]
[TD]1.04[/TD]
[TD]1.05[/TD]
[TD]1.05[/TD]
[/TR]
[TR]
[TD]858[/TD]
[TD][/TD]
[TD]1.01[/TD]
[TD]1.02[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]331[/TD]
[TD]1.03[/TD]
[TD]1.03[/TD]
[TD]1.03[/TD]
[TD]1.05[/TD]
[TD]1.05[/TD]
[/TR]
</tbody>[/TABLE]


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.
[TABLE="class: grid, width: 800, align: center"]
<tbody>[TR]
[TD]ID Number[/TD]
[TD]1.03[/TD]
[TD]1.04[/TD]
[TD]1.05[/TD]
[/TR]
[TR]
[TD]1433[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]625[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]331[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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