RGB values as criteria in SUBTOTAL or Aggregate count.

[vanwooten]

The attached image shows a color key at the top of column X. I have a function that returns the color key RGB values to the left of it. The table column below the key (Trade Notes) has these colors throughout and I have a table column (Color) that displays the RGB values for those colors. I am try to figure out a formula using SUBTOTAL or AGGREGATE to produce those percent values in column W next to the key. so W3 = count of yellows displayed in table / count of all rows being displayed in table.

 

[jasonb75]

It would have to be SUBTOTAL for what you're asking, AGGREGATE does not work correctly if you try to combine conditional criteria with filter / hidden row exclusions.

While I would expect it to be possible, as it is going to be utilising an existing UDF we would need the underlying code in order to be able to test any ideas.

Assuming that your function is named RGB, the theory would be something like

=SUMPRODUCT((SUBTOTAL(9,OFFSET($W$9,ROW($W$9:$W$1000)-ROW($W$9),0,1,1)=1)*(RGB($X$9:$X$1000)=V2))

Unlikely that it will be the correct formula but it is the best that I can do without testing.

The lower part of the image is appears to be a structured table e.g. one that uses references like Table1[@Profit%]. If it is then it would be better to use the names in the formula rather than cell ranges. On a side note, the colour banding on a table works differently to normal cell formatting so it is possible that your function will not recognise those cells correctly. As with the formula, this is an unknown factor without the function code for testing.

 

[vanwooten]

It would have to be SUBTOTAL for what you're asking, AGGREGATE does not work correctly if you try to combine conditional criteria with filter / hidden row exclusions.

While I would expect it to be possible, as it is going to be utilising an existing UDF we would need the underlying code in order to be able to test any ideas.

Assuming that your function is named RGB, the theory would be something like

=SUMPRODUCT((SUBTOTAL(9,OFFSET($W$9,ROW($W$9:$W$1000)-ROW($W$9),0,1,1)=1)*(RGB($X$9:$X$1000)=V2))

Unlikely that it will be the correct formula but it is the best that I can do without testing.

The lower part of the image is appears to be a structured table e.g. one that uses references like Table1[@Profit%]. If it is then it would be better to use the names in the formula rather than cell ranges. On a side note, the colour banding on a table works differently to normal cell formatting so it is possible that your function will not recognise those cells correctly. As with the formula, this is an unknown factor without the function code for testing.

This is the approach I ended up taking. Thanks.