Thanks for the reply, Macropod. I've attached an excel file that contains some formulae that I used, including the countifs and sumproduct together. Please see if there is another way to solve the problem. Many thanks.



Thanks Pecoflyer for the reply too. No, I just colored the cell for identification. No special formatting.

Attached Files

countifssumproduct.xlsm (24.7 KB, 13 views)

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

Hi Catalin B. I agree with you and Macropod that "=SUMPRODUCT(--(colorindex(A6:A25)=colorindex(D6))" will not give a result as a number in this case. Hence I made changes so that "Sumproduct" need not be used. Attached is the same file with the changes made.
Thanks for all the suggestions given.

Attached Files

countifssumproduct.xlsm (25.8 KB, 11 views)

I guess you did not read the entire message, so your efforts are in vain...
I gave you a replacement for your formula that will do the job. It's the one in red, in case you don't read this message