[Webinar] Streamline your web hosting managementRegister Today

x
Solved

# Excel formula to count number of cells with fill color that contain text, and number of cells with no fill that do not contain text

Posted on 2018-02-08
High Priority
47 Views
The attached spreadsheet uses conditional formatting to guide the data entry clerk in filling it out properly.

Column H and Column I have conditional formatting as follows:

If Column F includes the word 'YES', then all subsequent columns should be completed (cells have no fill color)
If Column F includes the word 'NO', then conditional formatting then changes cell color in Col H and I to orange, indicating to the user that these cells should not be filled out.

At the end of the production cycle, I would like to review the data entry for errrors. Specifically:

Col H and Col I:
# of cells with no fill color that are blank (formula result should be 4 cells in Col H, 3 cells in Col I)
# of cells with fill color that are not blank (formula result should be 2 cells in Col H, 1 cell in Col I)

Thanks,
Andrea
EE_ColorCells.xlsx
0
Question by:Andreamary

LVL 24

Accepted Solution

Ejgil Hedegaard earned 3000 total points
ID: 42463090
# of cells with no fill color that are blank (formula result should be 4 cells in Col H, 3 cells in Col I)
H: =SUMPRODUCT((RIGHT(\$F\$2:\$F\$24,3)="YES")*(H\$2:H\$24=""))
I: =SUMPRODUCT((RIGHT(\$F\$2:\$F\$24,3)="YES")*(I\$2:I\$24=""))
# of cells with fill color that are not blank (formula result should be 2 cells in Col H, 1 cell in Col I)
H: =SUMPRODUCT((RIGHT(\$F\$2:\$F\$24,2)="NO")*(H\$2:H\$24<>""))
i: =SUMPRODUCT((RIGHT(\$F\$2:\$F\$24,2)="NO")*(I\$2:I\$24<>""))
1

Author Closing Comment

ID: 42463223
Perfect! Thanks very much, Ejgil...

Andrea
0