Forum Discussion
EXcel SUMPRODUCT
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
In addition I only want 9 of the highest highlighted. If the numbers are duplicated and therefore more than 9 are highlighted, it must disregard any duplicate over and above the 9,
EG: if the string of numbers were all the same, say 12 numbers all 39 then only 9 of them must be highlighted.
Thks
to eliminate duplicate higher numbers you can create a secondary table where you eliminate the duplicates with 0. and in conditional formatting you can use this table's value in comparison and apply conditionin on the first table. chec the attached file. you can also see the duplicates removed in second table
