Counting rows with applicable criteria.

[scottw1105]

Hey everyone!

Ok, quick question which may or may not be difficult.

So I have 150 rows of NFL player names that simulate a 9 player fantasy football lineup, I want to be able to count specific occurrences of a combination of 3 players names in the same row. For example:
(Keep in mind each one of these names is in its own cell in a row, not one giant cell)

Kirk Cousins Leonard Fournette Kerryon Johnson DeSean Jackson Stefon Diggs Adam Thielen Austin Hooper Ezekiel Elliott Bengals
Patrick Mahomes Chris Thompson Leonard Fournette DeSean Jackson Tyreek Hill Sterling Shepard Travis Kelce Joe Mixon Panthers
Matthew Stafford Leonard Fournette Kerryon Johnson Adam Thielen Danny Amendola Christian Kirk Zach Ertz Ezekiel Elliott Dolphins
Philip Rivers Leonard Fournette Kerryon Johnson Tyler Lockett T.Y. Hilton Keenan Allen Zach Ertz Hunter Henry 49ers
Patrick Mahomes Dalvin Cook Chris Carson Marqise Lee Sammy Watkins Tyreek Hill Zach Ertz Kerryon Johnson Panthers
Kirk Cousins Dalvin Cook Joe Mixon Justin Hardy Adam Thielen Tyreek Hill George Kittle Kerryon Johnson Bengals
Dak Prescott Leonard Fournette Joe Mixon Calvin Ridley Adam Thielen Sterling Shepard Jason Witten Ezekiel Elliott Dolphins
Patrick Mahomes Leonard Fournette Damien Williams Adam Thielen Tyreek Hill Sterling Shepard Austin Hooper Chris Carson Panthers
Matthew Stafford Leonard Fournette Kerryon Johnson Tyreek Hill Christian Kirk Marvin Jones Jordan Reed Ezekiel Elliott Colts
Kirk Cousins Dalvin Cook Kerryon Johnson Justin Hardy DJ Moore Adam Thielen Zach Ertz Ezekiel Elliott Dolphins
Nick Foles Leonard Fournette Chris Carson Marqise Lee Robert Woods Tyreek Hill Austin Hooper Ezekiel Elliott Dolphins
Patrick Mahomes Mark Ingram Leonard Fournette Calvin Ridley Stefon Diggs Tyreek Hill Hunter Henry Damien Williams Bengals
Jameis Winston Leonard Fournette Joe Mixon Stefon Diggs Breshad Perriman Chris Godwin George Kittle Kerryon Johnson Bengals
Kirk Cousins Leonard Fournette Joe Mixon Justin Hardy Stefon Diggs Adam Thielen Travis Kelce Kerryon Johnson Panthers

Each one of these rows is a "lineup", if I want to know how many times say for instance the combination of Kirk Cousins, Stefon Diggs and Adam Thielen all appear in the same row, how would I do this?

[XOR LX]

Hi,

Array formula** in O2:

=SUM(0+(MMULT(COUNTIF(OFFSET(A$1:I$1,ROW(A1:I150)-MIN(ROW(A1:I150)),),L2:N2),{1;1;1})=3))

Note that the parts in red are static, and relate to the fact that you are looking to count the number of rows for which 3 entries are found. A more general and extendable set-up is:

=SUM(0+(MMULT(COUNTIF(OFFSET(A$1:I$1,ROW(A1:I150)-MIN(ROW(A1:I150)),),L2:N2),TRANSPOSE(COLUMN(L2:N2)^0))=COLUMNS(L2:N2)))

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).