I think moving the field of interest one column right each time you move down a row in the summary table is inherently a problem.
Instead, I have returned results for the entire results table (all fields) but transposed the result to give a column. Rather than the 'Hits Code' being used within a MATCH formula, it is then simply the transpose of the table headers. The MMULT is used to perform a row by row calculations on a 2D array (other aggregations process the entire array to give a single result).
Thanks Peter, i'll take a look at that, much appreciated.
Thank you again Peter, now that is way of looking at it I would never have been able to come up with and I will use your post to develop my understanding further. Thank you.
One thing I cant work out though, the named field 'Digit' seems to be floating depending upon where the cursor is. I may be missing something but I cant work it out / understand out to create it.
I'm also having a little trouble getting a value other 0 after hitting shift ctrl enter, this may be due to my lack of understanding the named field 'digit' of course. I amended my file Chandoo_GaryMc1.xls and applied your formula structure but can't seem to get it to work. My O8 cell formula adapted from your file:
= MMULT( TRANSPOSE( --(LEFT(Results[[FC1hits]:[FC25hits]],1)=TEXT(digit,"0")) ), --(Results[CO]="CY") ) -
Thanks.