Forum Discussion
EXcel SUMPRODUCT
Hi,
You do that by replacing this part:
ROW($1:$5)
With this:
ROW($1:$9)
You can also simplify the formula this way:
=SUMPRODUCT(LARGE(E2:Z2,ROW(A1:A9)))
This formula will create an array of the largest nine numbers, then add them.
With regards to the conditional formatting, you have to use this formula in a new conditional formatting rule:
=MATCH(E2,LARGE($E$2:$Z$2,ROW($A$1:$A$9)),0)
I hope you find this solution helpful
Regards
Hi, thank you for your help, replacing the 5 with 9 works fine. But with the conditional formatting it highlights all of the duplicate numbers, so it appears as if more numbers have been selected.
It needs an additional condition that only allows highlighting of 9 cells
Regards
Vic
Hi,
The rule highlights only the 9 largest number in the row as the screenshot below:
And this is including the duplicates.
The SUMPRODUCT works fine but when I input the conditional formatting rule I get the result below
Hi,
If you want to apply the rule to multiple rows, please select them and apply the rule to them.
But you have to change the conditional formatting formula a little bit as follows:
=MATCH(A2,LARGE($A2:$V2,ROW($A$1:$A$9)),0)
Where $A2:$V2 is the range of the numbers across all rows.
But please note that you have to use only a single dollar sign before the column reference, this is to lock the columns but not the rows to allow the formula to go down to other rows.
With regards to range A1:A9, it must be locked in all directions like this ($A$1:$A$9).
Please find the attached file to test this solution.
Hope that helps
