Multi Condition Test in Excel

I need testers for a project that started as a ticket item when I went through Wood Badge. My unit can't afford TroopMaster and I wanted something that when the scout sat down with me or the members of the board we could see what the scout had accomplished. Here I am five years later and now on the 20th revision and I have accidentally created a problem.  On the Dashboard page I have is to where it checks to see how many requirements have been completed by seeing if there is an "A" in the field.The formula currently in use is: =IF(7-COUNTIF(Sheet#,"A")=0,"C",7-COUNTIF(Sheet#,"A")).  The problem I created is that one of the fields it is looking at is now auto-populated by a date based on the date on another sheet in the workbook.  So now the field on the Dashboard never turns to C for complete.  I thought I found the answer with =IF(8-COUNTIF(Star,"<>"&"")=0,"C",8-COUNTIF(Star,"<>"&"")) but I discovered that when the criteria is tested it looks at the cells in the range that are pulling it's data from other worksheets, 3 cells in total, as being populated even though there is nothing there.  Also discovered this would not have worked cause the formulas in those cells do a math function until a criteria is met and then the cell displays an "A".   Any ideas on how to fix this so that it will count the field if a date appears in the field?

I have attached the template I am currently working on.
Scout-History-2.xlsx
BMCISAdminAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ejgil HedegaardCommented:
The 3 protected (calculated) cells in Star range contain a space when there is no date.

A date is a positive number, so use this formula for Star to calculate only dates (numbers).
=IF(8-COUNTIF(Star,">0")=0,"C",8-COUNTIF(Star,">0"))
A text (like the space) is treated as 0, and is thus not counted.

You don't have data validation in the Star range for Dates, so it is possible to type anything.
If you want to test for that, then change the formulas in the Star range to return "" instead of " " when no date, and then use this formula on the Dashboard sheet to check for not empty.
=IF(8-SUMPRODUCT(--(Star<>""))=0,"C",8-SUMPRODUCT(--(Star<>"")))
1
BMCISAdminAuthor Commented:
This worked for the date but it is not tallying any of the Boxes that require an "A" and it also subtracts from the number of requirements for the merit badges when they have not fulfilled the requirement.  I also tried putting the "No / Yes" drop-down list and it still does not work.
0
Ejgil HedegaardCommented:
I expected you used a date in all cells.
It is not clear to me what you type in the not locked cells.

Use a combined formula to count both numbers and A in the locked cells.
=IF(8-COUNTIF(Star,">0")-COUNTIF(Star,"A")=0,"C",8-COUNTIF(Star,">0")-COUNTIF(Star,"A"))
Add more Countif statements if you want to count something else like Yes, and use datavalidation on the cells to ensure the input.

Since a date is a number, any positive number will be counted, but you could check for a number high enough to be a date this year or later, change to what is appropriate.
Today has the number 42261, but it is easier to read using the Date function, here with 2015-01-01
=IF(8-COUNTIF(Star,">="&DATE(2015,1,1))-COUNTIF(Star,"A")=0,"C",8-COUNTIF(Star,">="&DATE(2015,1,1))-COUNTIF(Star,"A"))

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

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.