Forum Discussion

craven2295's avatar
craven2295
Copper Contributor
Jan 27, 2020

Formula to sumproduct exactly top 12 scores in a range or 20

hi experts

 

I am using the following formula to Sumproduct 12 scores from a range of 20 because I want the top 12 scores to be totalled. 

 

=SUMPRODUCT(LARGE(A1:S1,ROW(INDIRECT("1:"&MIN(12,COUNT(A1:S1))))))

 

 However, if the when the bottom 2 scores in the 12 are identical the formula will include the 12th and 13th score and so on.   In fact if you change every value in the range to the same number it will add up the entire range.   

 

How do I change the formula so that even when there are identical numbers it only gives me the sum of 12 values in the range?

 

Thanks

Stan

1 Reply

  • Twifoo's avatar
    Twifoo
    Silver Contributor

    craven2295 

    You simply have to modify your formula in T1, to this: 

    =SUMPRODUCT(LARGE(A1:S1,

    ROW($1:$12)))