Determine if a range of cell values meet a specific criteria.

[rugbyguy]

I posted the other day for some help with a fitness group spreadsheet and the forum was very helpful. I come again asking for advice / help.

The fitness group has daily tasks to complete for points (good tasks) and ways to gain negative points (bad tasks). There are 5 good tasks and 2 bad tasks. The individuals can have a day where they skip any one of the good tasks, or have a bad task and not be penalized. They can skip more than one separate task on different days or all the tasks on a single day and not be penalized as long as all good tasks are completed for the remainder of the week. If they meet these requirements they receive bonus points for each day.

I have the spreadsheet set up for the individual to enter whether they performed the good tasks (Y or N) and enter the number of bad tasks (number).

In each of the tables below the individual will receive the bonus points for each day of the week.

I would like to use formula(s) to automate the calculation of bonus point eligibility.

I can't seem to figure out a way which doesn't involve many different calculations, IF...THEN formulas, COUNTIF formulas, etc.

Thank you in advance for your help and advice.

[TABLE="width: 300"]
<tbody>[TR]
[TD][/TD]
[TD]Mo[/TD]
[TD]Tu[/TD]
[TD]We[/TD]
[TD]Th[/TD]
[TD]Fr[/TD]
[TD]Sa[/TD]
[TD]Su[/TD]
[/TR]
[TR]
[TD]Task #1[/TD]
[TD][/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD]Task #2[/TD]
[TD][/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD]Task #3[/TD]
[TD][/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD]Task #4[/TD]
[TD][/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD]Task #5[/TD]
[TD][/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD]Bad Task #1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bad Task #2[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 300"]
<tbody>[TR]
[TD][/TD]
[TD]Mo[/TD]
[TD]Tu[/TD]
[TD]We[/TD]
[TD]Th[/TD]
[TD]Fr[/TD]
[TD]Sa[/TD]
[TD]Su[/TD]
[/TR]
[TR]
[TD]Task #1[/TD]
[TD][/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD]Task #2[/TD]
[TD]y[/TD]
[TD][/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD]Task #3[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD][/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD]Task #4[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD][/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD]Task #5[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD][/TD]
[TD]y[/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD]Bad Task #1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bad Task #2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Can you show the expected outcome for both tables, along with an explanation of how you came to that outcome?