Can Countifs and Sumproduct work together?
I have a column I4:I80. In this column, all the cells have either text "NF" or "F". Some of these cells are coloured light grey and some are not. I want to have a formula that will count the number of NF or F that are only in the light grey cells.

This is the formula I used for counting the number of "F" :



=countifs(I4:I80,"=F", I4:I80,"=sumproduct(--(colorindex(I4:I80)=colorindex(I87)))")

I87 is the cell where I have the light grey colour highlighted for reference.

Why does the formula return a 0 (zero) for both NF and F? (I have 20 NF and 8 F in light grey coloured cells.)

Attached is the function for colorindex.

Can anyone help? Thank you

Attached Files

ColorIndex Function.docx (12.0 KB, 9 views)

It might make things easier if you could attach the workbook concerned, with some sample data (delete anything sensitive).

FWIW, it seems to me that, as coded, your formula couldn't possibly work, as:
=countifs(I4:I80,"=F", I4:I80,"=sumproduct(--(colorindex(I4:I80)=colorindex(I87)))")
says the third expression has to be, not the calculated result, but the literal string:
"=sumproduct(--(colorindex(I4:I80)=colorindex(I87)))"

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Is the coloring of the cells the result of some Conditional Formatting?

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

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, 11 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, 9 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