DCOUNTA

[Big Leeroy]

Hello,

I am having trouble with the DCOUNTA. Im not sure if it is really the solution im looking for. I would like to cound the number of instances a varable occures within a column that has several Key Words within each cell.

EXAMPLE:

      A                            B                 C
1  Key Words                      dog                2 
2  dog, cat, pig
3  house, farm, grass
4  dog, pig, farm
5  tree, flour, dirt

I would like the formula to display the total count of the instances of the value located in b1 that occure in the list A2-A5.


Thanks for your help.

 

[wigi]

Hi

I don't know if this efficient use of computer resources, but it works...

=SUMPRODUCT(--(LEN(A2:A5)>LEN(SUBSTITUTE(A2:A5,B1,""))))

confirmed with Cntrl-Shift-Enter

Wigi

 

[Big Leeroy]

Thanks alot. That worked GREAT!!!

 

[Aladin Akyurek]

Hello,

I am having trouble with the DCOUNTA. Im not sure if it is really the solution im looking for. I would like to cound the number of instances a varable occures within a column that has several Key Words within each cell.

EXAMPLE:

      A                            B                 C
1  Key Words                      dog                2 
2  dog, cat, pig
3  house, farm, grass
4  dog, pig, farm
5  tree, flour, dirt

I would like the formula to display the total count of the instances of the value located in b1 that occure in the list A2-A5.


Thanks for your help.

Book2
	A	B	C	D
1	Key Words	Dog	2
2	Cat, Dog	Fish	1
3	Snake, Fish	Fishy	1
4	Drive, Race	Cat	2
5	Tiger, Dog
6	Dogged, Obstinate
7	Fishy, CAT
Sheet1

C1, copied down:

=SUMPRODUCT(LEN(SUBSTITUTE($A$2:$A$7," ","")&",")-LEN(SUBSTITUTE(UPPER(SUBSTITUTE($A$2:$A$7," ","")&","),UPPER(B1)&",","")))/LEN(B1&",")

For invoking DCOUNTA instead, you'll need to study:

http://support.microsoft.com/default.aspx?scid=kb;en-us;282851