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

JasAujla

New Member
Joined
Nov 16, 2020
Messages
24
Office Version
  1. 365
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?

1612731156721.png


1612731320431.png
 
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
IJKLMNO
1PlayerTom
2TomPL20/21CompPL
3Jim, Tom, Tom, StomicPL20/21Yr20/21
4LC20/21Goals5
5DanPL20/215
6TomLC20/21
7PL20/21
8Ben, Tom, Ben, Tom, TompsonPL20/21
Goals
Cell Formulas
RangeFormula
O4O4=LET(tj," "&TEXTJOIN(", ",1,FILTER(I2:I8,(J2:J8=O2)*(L2:L8=O3),""))&",",(LEN(tj)-LEN(SUBSTITUTE(UPPER(tj)," "&UPPER(O1)&",","")))/(LEN(O1)+2))
O5O5=(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)
Thank you worked a treat, applied same concept to my file:

1612792732728.png
 
Upvote 0

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Thank you worked a treat,
You are welcome. Glad it worked for you. Thanks for the follow-up. :)

However, I would recommend not using whole column references. I assume that you have nowhere near 1,000,000+ rows?
Even if you have tens of thousands of rows, I would suggest changing those column ranges to, say, I$1:I$100000 and L$1:L$100000 etc
Just use anything that would comfortably be larger than any data you are likely to encounter.
 
Upvote 0
You are welcome. Glad it worked for you. Thanks for the follow-up. :)

However, I would recommend not using whole column references. I assume that you have nowhere near 1,000,000+ rows?
Even if you have tens of thousands of rows, I would suggest changing those column ranges to, say, I$1:I$100000 and L$1:L$100000 etc
Just use anything that would comfortably be larger than any data you are likely to encounter.
Great suggestion the formula was taking some time to work out the outcome but changing the data ranges has sorted this :)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top