CountIf With Calculated Range

#1
1
2
3
4
5
6
7
8

#2
Red
Yellow
Green
Blue
Violet

[Code] .....

I am trying to determine how many times a value occurs in each quartile in a data set, with the range of my quartiles changing for each row.

Above is a simplified sample of the data I am working with. What I would like to do is calculate the length of a quartile for each row, then determine whether "Red" is in the 1st, 2nd, 3rd, or 4th quartile

Right now I have two output tables. The first is a "Quartile" table, which for each row counts the number of values entered, divides by four, multiplies by the appropriate quartile and rounds down. For this I use the function

=rounddown(counta(range)/4*(quartile))
For Q1 in this dataset, that is =rounddown(counta($B2:$I2)/4*(1))

Then I have a table which calculates whether "Red" occurs in each quartile, with references to the cell values in my quartile table. My formula for Q1 here is

=countif(index(row#2,1,match(Q1 value,$row#1,0)):Index(row#2,1,match(Q1 value,$row#1,0)),"Red".

The actual formula (can be used if pasting table values into excel) is:

=countif(index($B2,$I2,1,match(Q1,$B$1:$I$1,0)):index($B2:$I2,1,match(Q1,$B$1:$I$1,0)),"Red")

Where Q1 equals my rounddown formula noted above, returning "1" in this case. For subsequent quartiles, I change my reference in the Match formula to start with the previous quartile +1, and end with the current quartile.