Nesting Count & Search Function - it it possible (or alternative)?

[Lycanthrope74]

Hi All

New to the boards & wondered if you could help me with a challenge: -

I have one worksheet showing a pattern of 10 matches weekly football scores. I have another sheet showing all possible combinations of three scores 125,000 possible combinations. I can tell from a simple Countif formula =

=COUNTIF('Weekly Patterns'!B:B, "*"&E3&"*") that pattern 0-0, 1-1, 1-1 has occurred 7 times in 8 seasons. I now want to find out how many times that pattern has occurred starting at Position 1 in the weekly set of ten, how many times from position 2, how many times from position 3 etc etc up to & including position 8.

I'm guessing I need to somehow Nest a FIND or SEARCH Function with a COUNTIF or COUNTIFS function. I have tried seemingly infinite variants of the following: -

=COUNTIFS('3 Combos'!B:B,SEARCH('3 Combos'!E3,'Weekly Patterns'!B:B,4))

(I have position 4 as the starting Position in the SEARCH because i know i have two of the 7 total matches atrting in Position 4 so, I should get 2 as the formulas result then 1 for position 5, 3 for position 6 & 1 for position 7 which is the 7 the COUNTIF is finding)

I'm therefore expecting a result of 2 for the nested formula but, though it is a legal formula & doesn;t return any error, I continually get zero as the answer regardless of what starting position I choose or, however, I try to vary the formula (i've even tried nesting the COUNTIFS inside SEARCH but, that doesn't work either).

I think venturing into Array formulas may be the only way to tackle this(?) but, they are a little beyond the scope of my current Excel knowledge(?); would anyone else know how i can tackle this using standard formulas (nested or otherwise) &, if it has to be an array, what that would look like?

Many thanks in hope more than anything else

Scott.