Forum Discussion

CoadyKrZY's avatar
CoadyKrZY
Copper Contributor
Apr 02, 2019

Count Conditional Formatted Cells

I am trying to create a formula in Column/Row P3, P4, and P5 that will count the number of conditionally formatted cells in Column I3:I24 based on the color of the cell.   I have attached an example of my spreadsheet.

 

Thanks

 

Coady

excel

3 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Apr 02, 2019

    CoadyKrZY , I'd change a bit formulas for conditional formatting rules to exclude blanks, like (for red)

    =AND(E3+5 < TODAY(),LEN(E3)>0)

    Relative counting, as in previous post, like

    =SUMPRODUCT(($E$3:$E$24+5 < TODAY())*(LEN($E$3:$E$24)>0))

    Similar for another colors, added in attached file

    Of course, that's only one of variants.

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Apr 02, 2019

    CoadyKrZY 

    I agree with jurgentas. 

    Test the conditions that lead to the formatting.  Instead of performing the calculations cell by cell with relative references you will need the calculation to use entire columns as arrays and to sum the result.  In order to implement a row-by-row AND condition use the product "*" of the two parts of the condition; also convert any TRUE/FALSE to 1/0 before summing.