Count a Word occurrence in a column number of times

[abdulads]

Grade (wanted to count in a table how many times A1 or B1 ora2 or b2 is occuring and if its occuring in a cell two times then count only half(0.5) and if 3 Grades then (0.33)

A1, B1

B1

B2

A2

A2,B2

B1,B2

 

[Kaper]

With helper column B where 1/2; 1/3 etc is calculated:
See below for formulas for B2 to be copied down and D2 to be copied right of course D1; E1, ... are the possible grades

Book1
	A	B	C	D	E	F	G	H	I
1	Grade	helper		A1	A2	B1	B2	A3
2	A1, B1	0,5		0,5	1,5	2	2	0
3	B1	1
4	B2	1
5	A2	1
6	A2,B2	0,5
7	B1,B2	0,5
8
Sheet2

Cell Formulas
Range	Formula
D2:H2	D2	=SUMPRODUCT(ISNUMBER(FIND(D1,$A$2:$A$100))*$B$2:$B$100)
B2:B7	B2	=1/(LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1)

 

[kvsrinivasamurthy]

Without helper column,. ARRAY formula in E2 copied down.
=SUM(IF(ISNUMBER(FIND(D2,TRANSPOSE($A$2:$A$7))),1/MMULT(1*TRANSPOSE(ROW($D$2:$D$5)>0),1*ISNUMBER(FIND($D$2:$D$5,TRANSPOSE($A$2:$A$7)))),0))

Book1
	A	B	C	D	E	F
1
2	A1, B1			A1	0.5
3	B1			B1	2
4	B2			A2	1.5
5	A2			B2	2
6	A2,B2
7	B1,B2
8
Sheet3

Cell Formulas
Range	Formula
E2:E5	E2	=SUM(IF(ISNUMBER(FIND(D2,TRANSPOSE($A$2:$A$7))),1/MMULT(1*TRANSPOSE(ROW($D$2:$D$5)>0),1*ISNUMBER(FIND($D$2:$D$5,TRANSPOSE($A$2:$A$7)))),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.

 

[Peter_SSs]

@abdulads
Please update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Peter_SSs]

.. or just combine the two formulas from post 2 into one

25 03 10.xlsm
	A	B	C	D	E
1
2	A1, B1			A1	0.5
3	B1			B1	2
4	B2			A2	1.5
5	A2			B2	2
6	A2,B2			A3	0
7	B1,B2
Sheet2 (3)

Cell Formulas
Range	Formula
E2:E6	E2	=SUMPRODUCT(ISNUMBER(FIND(D2,A$2:A$7))/(LEN(A$2:A$7)-LEN(SUBSTITUTE(A$2:A$7,",",""))+1))

 

[abdulads]

With helper column B where 1/2; 1/3 etc is calculated:
See below for formulas for B2 to be copied down and D2 to be copied right of course D1; E1, ... are the possible grades

Book1
	A	B	C	D	E	F	G	H	I
1	Grade	helper		A1	A2	B1	B2	A3
2	A1, B1	0,5		0,5	1,5	2	2	0
3	B1	1
4	B2	1
5	A2	1
6	A2,B2	0,5
7	B1,B2	0,5
8
Sheet2

Cell Formulas
Range	Formula
D2:H2	D2	=SUMPRODUCT(ISNUMBER(FIND(D1,$A$2:$A$100))*$B$2:$B$100)
B2:B7	B2	=1/(LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1)

Thank you so much.

 

[abdulads]

Without helper column,. ARRAY formula in E2 copied down.
=SUM(IF(ISNUMBER(FIND(D2,TRANSPOSE($A$2:$A$7))),1/MMULT(1*TRANSPOSE(ROW($D$2:$D$5)>0),1*ISNUMBER(FIND($D$2:$D$5,TRANSPOSE($A$2:$A$7)))),0))

Book1
	A	B	C	D	E	F
1
2	A1, B1			A1	0.5
3	B1			B1	2
4	B2			A2	1.5
5	A2			B2	2
6	A2,B2
7	B1,B2
8
Sheet3

Cell Formulas
Range	Formula
E2:E5	E2	=SUM(IF(ISNUMBER(FIND(D2,TRANSPOSE($A$2:$A$7))),1/MMULT(1*TRANSPOSE(ROW($D$2:$D$5)>0),1*ISNUMBER(FIND($D$2:$D$5,TRANSPOSE($A$2:$A$7)))),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.

Thank you so much.

 

[abdulads]

.. or just combine the two formulas from post 2 into one

25 03 10.xlsm
	A	B	C	D	E
1
2	A1, B1			A1	0.5
3	B1			B1	2
4	B2			A2	1.5
5	A2			B2	2
6	A2,B2			A3	0
7	B1,B2
Sheet2 (3)

Cell Formulas
Range	Formula
E2:E6	E2	=SUMPRODUCT(ISNUMBER(FIND(D2,A$2:A$7))/(LEN(A$2:A$7)-LEN(SUBSTITUTE(A$2:A$7,",",""))+1))

Thank you so so much it worked

 

[Peter_SSs]

Thank you so so much it worked

You're welcome. Glad we could help. Thanks for the follow-up.

.. now could your please update your version info as requested in post 4?