Jan 27 2020 01:59 PM
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
Jan 27 2020 06:16 PM
You simply have to modify your formula in T1, to this:
=SUMPRODUCT(LARGE(A1:S1,
ROW($1:$12)))