[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
2
High Priority
?
47 Views
Last Modified: 2018-02-08
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
Comment
Question by:Andreamary
2 Comments
 
LVL 24

Accepted Solution

by:
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

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

Andrea
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Join & Write a Comment

This is an article on how to answer questions, earn points and become an expert.
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question