Vlookup and countif

[LittleStalky]

Hi,

I'm trying to pull through data from one sheet into my main sheet using a combination of the vlookup and countif functions. What I'm trying to do is use the vlookup function to find a match in my second sheet, and then pull through a count for however many times "CIF" appears in the columns. For example:

Sheet 1

	A	B	C
1	ID	Name	CIF count
2	123	Bob	3
3	456	John	1
4	789	Jane	0

<tbody>
</tbody>

Sheet 2

	A	B	C	D	E
1	ID	2016	2015	2014	2013
2	123		CIF	CIF	CIF
3	456		CIF
4	789

<tbody>
</tbody>

I'm currently using this formula - =IF(ISERROR(VLOOKUP(A2,Summit!A:BH,60,FALSE)),"0",COUNTIF(Summit!2:2,"CIF"))This seems to work fine assuming that the data in sheet 2 remains in the same order. If I sort the data then the results in sheet 1 get all out of whack, which I can see is because the formula is then referencing the wrong cell.

Is there a way I can pull this data through and still be able to sort data in both sheets? Am I going about this completely the wrong way? All of my ID numbers are 100% unique so I thought I'd be able to count the corresponding data based on a match via vlookup.

I'm using Excel 2013 on Windows 8.

Any help would be greatly appreciated.

Thanks

Sarah

 

[MARZIOTULLIO]

	A​	B​	C​	D​	E​
1​	ID	Name	CIF count		sheet1
2​	123​	Bob	3​
3​	456​	John	1​
4​	789​	Jane	0​

<tbody>
</tbody>

	A​	B​	C​	D​	E​	F​	G​	H​
1​	1​	ID	2016​	2015​	2014​	2013​		sheet2
2​	2​	123​		CIF	CIF	CIF
3​	3​	456​		CIF
4​	4​	789​

<tbody>
</tbody>

sheet1


c2 =SUMPRODUCT((Sheet2!$B$2:$B$4=Sheet1!$A2)*(Sheet2!$C$2:$F$4=Sheet2!$D$2)) copy down

 

[LittleStalky]

Thank you so much! This works wonderfully.

	A​	B​	C​	D​	E​
1​	ID	Name	CIF count		sheet1
2​	123​	Bob	3​
3​	456​	John	1​
4​	789​	Jane	0​

<tbody>
</tbody>

	A​	B​	C​	D​	E​	F​	G​	H​
1​	1​	ID	2016​	2015​	2014​	2013​		sheet2
2​	2​	123​		CIF	CIF	CIF
3​	3​	456​		CIF
4​	4​	789​

<tbody>
</tbody>

sheet1


c2 =SUMPRODUCT((Sheet2!$B$2:$B$4=Sheet1!$A2)*(Sheet2!$C$2:$F$4=Sheet2!$D$2)) copy down