Help with formula results

ianshaw2  wrote:  ``SUMIF(L3:AJ3,">="&LARGE(L3:AJ3,10)) - this does not give the sum of the top ten``

You are correct.  But because you do not identify any cells in question and explain the expected results, I am not sure I have identified the problem of interest.

ERRATA....  I had not seen your PM, in which you do indeed identify some of the rows that I flag as erroreous below.

The SUMIF formula works in most cases only by coincidence either because the 10th largest value is zero, or because there are indeed no more than 10 values >= the 10th largest value.

However, the SUMIF produces the wrong result when there are multiple cells that match the 10th largest value.

This is demonstrated below.  See the attached file for details.

In row 158, for example, the 10 largest values are {24,21,20,29,32,24,25,27,25,24}.  But since 20 is the 10th largest, SUMIF includes the 3 duplicate 20s as well.

However, the average in I158 always divides by 10, not 13.

The correct formula is demonstrated in AL158, to wit:

=SUM(LARGE(L158:AJ158, {1,2,3,4,5,6,7,8,9,10}))

In Office 365 Excel and some later versions of Excel, the array constant {1,...,10} can be replaced with the expression SEQUENCE(1,10).

-----

The formula in J3 is completely wrong, to wit:  =A60:AC60.

It appears to return the value in J60, due to the implicit intersection feature.