Counting Individual Numbers Within Cells

[rkoehler58]

I have a sheet which contains the following:

Column A Column B Column C Column D
Safe 4, 10 Hot 3
Safe 1, 3, 5 Hot 2, 5
Safe 13 Hot 7

Columns B and D may contain multiple entries within a cell.

I am trying to develop a formula to calculate the total number of entries for "Safe and "Hot".

In my example, the number of entries for "Safe" will be 6, while the number of entries for "Hot" would be 4.

Can anyone suggest a way to do this?

 

[Snakehips]

rkoehler,

If the entries will always be separated by a comma then maybe something like.....

Excel 2007
	A	B	C	D	E	F
1					Safe	Hot
2	Safe	4, 10	Hot	3	9	7
3	Safe	1,3, 5	Hot	2, 5
4	Safe	13	Hot	7
5	Safe	1, 2, 3	Hot	1, 2, 3
6
Sheet6

Cell Formulas
Range	Formula
E2	{=SUM(LEN(B2:B20)-LEN(SUBSTITUTE(B2:B20,",",""))+IF(B2:B20="",0,1))}
F2	{=SUM(LEN(D2:D20)-LEN(SUBSTITUTE(D2:D20,",",""))+IF(D2:D20="",0,1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Hope that helps.

 

[rkoehler58]

rkoehler,

If the entries will always be separated by a comma then maybe something like.....
Excel 2007

	A	B	C	D	E	F
1					Safe	Hot
2	Safe	4, 10	Hot	3	9	7
3	Safe	1,3, 5	Hot	2, 5
4	Safe	13	Hot	7
5	Safe	1, 2, 3	Hot	1, 2, 3
6

<colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet6​

Array Formulas Cell Formula E2 {=SUM(LEN(B2:B20)-LEN(SUBSTITUTE(B2:B20,",",""))+IF(B2:B20="",0,1))} F2 {=SUM(LEN(D2:D20)-LEN(SUBSTITUTE(D2:D20,",",""))+IF(D2:D20="",0,1))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Hope that helps.

That works great. Thanks for your help.