Find and Count Instances

[madhuchelliah]

Hello Folks, I want to check the instances of the cells in C column of sheet1 in sheet2. In the D column i want how many instances it is there in the sheet2. For example if i have 200 value in c column in sheet1 the macro should check sheet2 for 200 and return with how many instances it is there in sheet2. Note: i have lot of values in C column.

 

[Marcelo Branco]

Which column of Sheet2 should be searched to find the values in Sheet1 column C?

M.

 

[madhuchelliah]

Which column of Sheet2 should be searched to find the values in Sheet1 column C?

M.

No specific column. Through out the sheet2

 

[Marcelo Branco]

Say your data are Sheet2 columns A:Z (adjust to suit)
=COUNTIF(Sheet2!A:Z,C2)

M.

 

[madhuchelliah]

Say your data are Sheet2 columns A:Z (adjust to suit)
=COUNTIF(Sheet2!A:Z,C2)

M.

Thanks Marc, it will be more helpful if its in macro format.

 

[Marcelo Branco]

Maybe this UDF (User Defined Function)

Function CountSh2(v As Variant)
    CountSh2 = Application.CountIf(Sheets("Sheet2").UsedRange, v)
End Function

In Excel
D2
=CountSh2(C2)

M.