asked on

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

Ejgil Hedegaard

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<>"")))

BMCISAdmin

ASKER

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.

ASKER CERTIFIED SOLUTION

Ejgil Hedegaard

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial