Formula to find score by counting numbers in a cell

[jack109]

home_winlay_away (10).csv
	A	B	C	D	E
1	goal times			desired result
2	home	away		home	away
3	24 62	45		2	1
4	76	48		1	1
5	30 75	49		2	1
6	48	32 35 41 62		1	4
7		4 19		0	2
8		15 61		0	2
9	72	17		1	1
10	61	30 52 54		1	3
11				0	0
12	9 32	12 51		2	2
13	8 42	3 68		2	2
14	90			1	0
15	45 59	67 78		2	2
16		83		0	1
17		19 36 44		0	3
18	75	35		1	1
19	52	12		1	1
Sheet1

Hi

Is there a formula that can count the numbers in a cell in order to work out a score? So instead of manually inputting the scores in col D & E a formula would work it out.

Cheers

 

[arthurbr]

In D3,try

=LEN(A3)-LEN(SUBSTITUTE(A3," ",""))+1*(A3<>"")

pull one right then down

 

[Joe4]

Enter this formula in cell D3 and copy down to E19:

=IF(A3="",0,LEN(A3)-LEN(SUBSTITUTE(A3," ",""))+1)

 

[Fluff]

Another option

=IF(A3="",0,ROWS(TEXTSPLIT(A3,," ")))

 

[Fluff]

Another option in D3 only

=MAP(A3:B19,LAMBDA(m,IF(m="",0,ROWS(TEXTSPLIT(m,," ")))))

 

[jack109]

Last two work perfect, thank you.

athurbr yours works fine unless there is no numbers in a cell where it returns 1 instead of 0.

 

[Joe4]

You are welcome.
Glad we were able to help!

 

[arthurbr]

Last two work perfect, thank you.

athurbr yours works fine unless there is no numbers in a cell where it returns 1 instead of 0.

I don't think so

 

[Scott Huish]

Another option in D3 only

=MAP(A3:B19,LAMBDA(m,IF(m="",0,ROWS(TEXTSPLIT(m,," ")))))

A litle shorter:

=MAP(A3:B19,LAMBDA(m,COUNT(--TEXTSPLIT(m,," "))))