Explanation
The core of this formula is the COUNTA function, configured with an expanding range like this:
COUNTA($B$5:B5)
As the formula is copied down the column, the range starting with B5 expands to include each new row, and COUNTA returns a running count of all non-blank entries in the range.
The result of COUNTA is then divided by “size”, configured as a named range F4. Using a cell on the worksheet for group size allows the grouping to be changed at any time without editing the formula. The named range is used only for readability and convenience.
The resulting value is then processed by the CEILING function, with a significance of 1. CEILING is a rounding function that always rounds up to the next unit of significance. In this example, this causes fractional values to be rounded up to the next integer.
Handling empty cells
If the range you are counting contains blank or empty cells, you can wrap the formula inside the IF function like this:
=IF(B5<>"",CEILING(COUNTA($B$5:B5)/size,1),"")
Here, we run the counting and rounding operation described above only when the cell in column B is not blank. If it is blank, we skip the count and return an empty string ("").
Explanation
Inside the AND function there are two logical criteria. The first is straightforward, and ensures that only cells that match the color in E5 are highlighted:
$B3=$E$5
The second test is more complex:
$C3<=SMALL(IF(color=$E$5,amount),3)
Here, we filter amounts to make sure that only values associated with the color in E5 (blue) are retained. The filtering is done with the IF function like this:
IF(color=$E$5,amount)
The resulting array looks like this:
{FALSE;100;FALSE;200;FALSE;300;FALSE;400;FALSE;500}
Notice the value from the amount column only survives if the color is “blue”. Other amounts are now FALSE.
Next, this array goes into the SMALL function with a k value of 3, and SMALL returns the “3rd smallest” value, 300. The logic for the second logical test reduces to:
$C3<=300
When both logical conditions are return TRUE, the conditional formatting is triggered and cells are highlighted.
Note: this is an array formula, but does not require control + shift + enter.
