Formula help....

[Giri]

Hi,

Suppose B1=abc, b2=bd, b3=xybz, b4=pqr. Suppose C1=b. What
is the formula to set at A1 such that A1=3 since the
contents of C1 exists in the range of B1:B4 3 times
i.e "b" is occurring in B1:b4 3 times.
Any help in this regard is greatly appreciated. Thank You.

Regards,
Giri

 

[Giri]

Hi Jerry,

The solution does not return "3" that i wanted given the
example as explained earlier. "Find" function takes only 1
cell and not a range of cells for its search.
Kindly revert if u think that i have done something wrong.

Regards,
Giri

 

[Chip Pearson]

Giri,

You have to enter the formula as an array formula. Type the
formula, but don't hit Enter. Instead, press CTRL SHIFT ENTER.
You must do this when you first enter the formula and whenever you
edit it later. If you do this properly, Excel will display the
formula enclosed in curly braces {}.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)