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

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
AndreamaryAsked:
Who is Participating?
 
Ejgil HedegaardConnect With a Mentor Commented:
# 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
 
AndreamaryAuthor Commented:
Perfect! Thanks very much, Ejgil...

Andrea
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.