Jan 24 2021 11:44 AM
Jan 24 2021 12:31 PM
Hi @Lis981965 ,
I notice that the automatic translation bungled up your question quite a bit, so here is the translation with some adjustments, especially the formulas.
Hello. I've created a table where I want to use conditional formatting to highlight the three largest and three smallest values. Hidden rows should be ignored.
But for me, this only works for the largest values. There I determined the values for cells Q7:Q246.
For this purpose, I have in column AA =Subtotal(9; Q7) and this function is then specified for all rows. In cell AB7 I entered the number 3. Now I have marked the cells Q7:Q246. -> conditional formatting -> formula:
=Countif($AA$7:$AA$246;">"&AA7)<$AB$7
and then apply the format. That also works fine ...
For the smallest values, I entered in the conditional function:
=Countif($AA$7:$AA$246;"<"&AA7)<$AB$7
This highlights the smallest three values, but only for all rows. In other words, if I hide rows, they will still be taken into account. However, I would like to take this into account only the figures shown.
Do you have an idea how this can work? For the function for the largest values, it works with the specified function.
Thank you very much!