Multiple countifs to find key words even if they appear in one row multiple times

[JasAujla]

Hi All,

I have a spreadsheet for football stats and need help with the following:

The formula below has three criteria - How many goals has 'Fernandes' scored in the 'PL' in '20/21'

In the second picture you can see that Fernandes is listed more than one time in one row but the CCOUNTIF formula only counts first instance.

What is the formula to count all instances of keywords in one row in a range?

 

[orsm6]

try sumifs.

 

[orsm6]

Hi All,

I have a spreadsheet for football stats and need help with the following:

The formula below has three criteria - How many goals has 'Fernandes' scored in the 'PL' in '20/21'

In the second picture you can see that Fernandes is listed more than one time in one row but the CCOUNTIF formula only counts first instance.

What is the formula to count all instances of keywords in one row in a range?

View attachment 31474

View attachment 31476

or if you wanted to count by a player specifically.... try this.

=sumifs('OGS Record'!J:J,'PL Stats 21'!I2,'OGS Record'!L:L,'PL Stats 21'!H1,'OGS Record'!I:I,'OGS Record'!I:I,"*"&H3&"*")

what i tried to do here is sum what your looking for based on the name you enter in cell H3 above in your image. hope that helped and made sense.

 

[jtakw]

Hi All,

In the second picture you can see that Fernandes is listed more than one time in one row but the CCOUNTIF formula only counts first instance.

What is the formula to count all instances of keywords in one row in a range?

Hi,

I'm only addressing this part of your question, as I'm not understanding what the other requirements are, and your pictures and descriptions are not helping:

E1 formula will count how many times a Keyword (D1) is in a single cell,
E2 formula will count how many times a Keyword (D2) is in a Range of cells:

Book3.xlsx
	A	B	C	D	E
1	one three one two one one one one one two two three three two three three one			one	8
2	one three one two one one one one one two two three three two three three one	three one	two one four	one	10
Sheet741

Cell Formulas
Range	Formula
E1	E1	=(LEN(A1)-LEN(SUBSTITUTE(A1,D1,"")))/LEN(D1)
E2	E2	=SUMPRODUCT((LEN(A2:C2)-LEN(SUBSTITUTE(A2:C2,D2,"")))/LEN(D2))

 

[orsm6]

i've had another look.... what is difficult for us is:

- we don't know where you need the formula is going (what sheet and cell number etc)

 

[orsm6]

I have managed to make this formula work.... but as i said, we don;t know where you are putting

=SUMIFS('OGS Record'!M:M,'OGS Record'!J:J,'PL Stats 21'!I2,'OGS Record'!L:L,'PL Stats 21'!H1,'OGS Record'!I:I,"*"&'PL Stats 21'!A2&"*")

i put the above formula on a random sheet called sheet3 cell H3....
but for sum ifs, you need the column with the number of goals kicked per game..... that's why i have at the start 'OGS Record'!M:M.... this needs to change to where ever the goals per game is i'd assume.

 

[JasAujla]

Hi,

Thanks for your reply please see examples below, the formula you provided is bring '0' at the moment.

Concept - Count how many gaols Fernandes has scored in the PL, 20/21

OGS Record

PL Stats 21

 

[JasAujla]

I need a formula to count players name even if it appears in one row multiple times, there is no additional columns for goals scored:

In the example above the formula should display = 3 goals for Fernandes - In the PL - In 20/21

 

[Peter_SSs]

Better if you can use XL2BB to provide sample data it will get you faster help and/or more helpers since they will not have to manually type out the sample data to test with.

See if you can adapt this to your layout and data. If you do noy yet have the LET function, use the O5 formula instead.

21 02 08.xlsm
	I	J	K	L	M	N	O
1						Player	Tom
2	Tom	PL		20/21		Comp	PL
3	Jim, Tom, Tom	PL		20/21		Yr	20/21
4		LC		20/21		Goals	5
5	Dan	PL		20/21			5
6	Tom	LC		20/21
7		PL		20/21
8	Ben, Tom, Ben, Tom	PL		20/21
Goals

Cell Formulas
Range	Formula
O4	O4	=LET(tj,TEXTJOIN(",",1,FILTER(I2:I8,(J2:J8=O2)*(L2:L8=O3),"")),(LEN(tj)-LEN(SUBSTITUTE(UPPER(tj),UPPER(O1),"")))/LEN(O1))
O5	O5	=(LEN(TEXTJOIN(",",1,FILTER(I2:I8,(J2:J8=O2)*(L2:L8=O3),"")))-LEN(SUBSTITUTE(UPPER(TEXTJOIN(",",1,FILTER(I2:I8,(J2:J8=O2)*(L2:L8=O3),""))),UPPER(O1),"")))/LEN(O1)

 

[Peter_SSs]

Actually, disregard that last suggestion as it is not robust. For example, with the sample data below, the previous formulas incorrectly return 7 as the count includes Tompson and Stomic when it shouldn't.
Try the relevant one of these instead.

21 02 08.xlsm
	I	J	K	L	M	N	O
1						Player	Tom
2	Tom	PL		20/21		Comp	PL
3	Jim, Tom, Tom, Stomic	PL		20/21		Yr	20/21
4		LC		20/21		Goals	5
5	Dan	PL		20/21			5
6	Tom	LC		20/21
7		PL		20/21
8	Ben, Tom, Ben, Tom, Tompson	PL		20/21
Goals

Cell Formulas
Range	Formula
O4	O4	=LET(tj," "&TEXTJOIN(", ",1,FILTER(I2:I8,(J2:J8=O2)*(L2:L8=O3),""))&",",(LEN(tj)-LEN(SUBSTITUTE(UPPER(tj)," "&UPPER(O1)&",","")))/(LEN(O1)+2))
O5	O5	=(LEN(" "&TEXTJOIN(", ",1,FILTER(I2:I8,(J2:J8=O2)*(L2:L8=O3),""))&",")-LEN(SUBSTITUTE(UPPER(" "&TEXTJOIN(", ",1,FILTER(I2:I8,(J2:J8=O2)*(L2:L8=O3),""))&",")," "&UPPER(O1)&",","")))/(LEN(O1)+2)