Hi Algo,
This function has some problems:
=COUNTIFS(A6:A25;"=F"; A6:A25;"=SUMPRODUCT(--(colorindex(A6:A25)=colorindex(D6))")
If you put this criteria like this: "=SUMPRODUCT(--(colorindex(A6:A25)=colorindex(D6))",
the function will check if the provided range contains the text between "".
Obviously, you want the criteria to be the result of Sumproduct function. For this, the criteria should look like: (without "" and equal sign):
SUMPRODUCT(--(colorindex(A6:A25)=colorindex(D6))
Another problem is that the result of Sumproduct function is a number: 11 in your case...which means that you count in that range, the cells that contains the number 11. Again, it's obvious that there is no number in that range... so the result is zero.
Your formula should look like:
=COUNTIFS(A6:A25;"=F";colorindex(A6:A25);colorinde x(D6))
But the problem is that COUNTIFS function does not accept this type of range: colorindex(A6:A25), so it will fail...
You have more than 2 options:
-use this formula: =SUM(IF((A6:A25="F")*(colorindex(A6:A25)=colorinde x(D6));1)) confirmed with CSE
-use this formula:
=COUNTIFS(A6:A25;"=F";C6:C25;colorindex(D6)), after you use in C6:C25 the formula colorindex(A6) copied down to extract the color index of range A6:A25
-another option is to wait for a better solution