I am totally stuck.

[cosmicsport]

I have a column range(V:V) of cells containing multiple entries separated by a comma (,). I have another column (AD) containing single entries.

I would like to find a formula not VBA that counts all instances of a string (like "Make more money") in range V:V happening for one certain instance in AD (like "Lab owner").

I got so far (see below) but it does not count the instances when "Make more money" is in an array of entries separated by a comma.

(like "Become a better technician., Make more money., Get a promotion to manager/supervisor/teamlead")

What can I do ????

Thanks in advance for your help!!!

=SUMPRODUCT(--(V:V="Make more money."),--(AD:AD="Lab owner"))

This gives out 2 but it should be 6.

 

[jtakw]

Hi,

Do you mean something like this:

Excel 2010
	V	AD	AE	AF
1
2	Become a better technician., Make more money., Get a promotion to manager/supervisor/teamlead			2
3		Lab owner
4	Become a better technician., Make more money., Get a promotion to manager/supervisor/teamlead	Lab owner
5	Make less money	Lab owner
6	No need to make more money	Lab owner
Sheet3

Cell Formulas
Range	Formula
AF2	=SUMPRODUCT((ISNUMBER(SEARCH("Make more money",V1:V6))*(AD1:AD6="Lab owner")))

 

[Eric W]

Or:

=COUNTIFS(V:V,"*make more money*",AD:AD,"lab owner")