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
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]CIF count[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]123[/TD]
[TD]Bob[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]456[/TD]
[TD]John[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]789[/TD]
[TD]Jane[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ID[/TD]
[TD]2016[/TD]
[TD]2015[/TD]
[TD]2014[/TD]
[TD]2013[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]123[/TD]
[TD][/TD]
[TD]CIF[/TD]
[TD]CIF[/TD]
[TD]CIF[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]456[/TD]
[TD][/TD]
[TD]CIF[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]789[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]CIF count[/TD]
[TD][/TD]
[TD]sheet1[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

123​

[/TD]
[TD]Bob[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

456​

[/TD]
[TD]John[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

789​

[/TD]
[TD]Jane[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]






[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD]ID[/TD]
[TD]

2016​

[/TD]
[TD]

2015​

[/TD]
[TD]

2014​

[/TD]
[TD]

2013​

[/TD]
[TD][/TD]
[TD]sheet2[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

2​

[/TD]
[TD]

123​

[/TD]
[TD][/TD]
[TD]CIF[/TD]
[TD]CIF[/TD]
[TD]CIF[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

3​

[/TD]
[TD]

456​

[/TD]
[TD][/TD]
[TD]CIF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

789​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]CIF count[/TD]
[TD][/TD]
[TD]sheet1[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

123​

[/TD]
[TD]Bob[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

456​

[/TD]
[TD]John[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

789​

[/TD]
[TD]Jane[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]






[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD]ID[/TD]
[TD]

2016​

[/TD]
[TD]

2015​

[/TD]
[TD]

2014​

[/TD]
[TD]

2013​

[/TD]
[TD][/TD]
[TD]sheet2[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

2​

[/TD]
[TD]

123​

[/TD]
[TD][/TD]
[TD]CIF[/TD]
[TD]CIF[/TD]
[TD]CIF[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

3​

[/TD]
[TD]

456​

[/TD]
[TD][/TD]
[TD]CIF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

789​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


sheet1


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