Count if all weekly tasks completed

[quentin5000]

Hi all

I hope someone can help me with a formula I am trying to create.

I have a list of scheduled tasks which each have to be done at least once per week. All I want to to do is use a count formula to check whether each task was marked as completed ("yes") at least once throughout the week.

What makes it more complicated though is that I want to use a formula to first find the term "Weekly Tasks" in column A, and when it finds it offset down and across to start the count from column B. I would like the formula to continue to count each row until it reaches the term "Other Tasks". At this point the formula is to stop counting in this manner.

If the formula determines that all rows in the range contain at least one "yes" for the week then it should return "complete", if not all weekly tasks were completed (i.e. one or more rows does not contain at least one "yes" per row the return value should be "check"

I have attached a work sheet with a mocked up example for reference.

Thank you all

[FDibbins]

I have a feeling that your sample file is far too simple, relative to your working document?

Will there always only ever be 1 yes per row?
Because if so, a simple =COUNTIF($B5:$H5,"yes")=1 should work for you?

[Saqib Qureshi]

This formula should do

Please Login or Register  to view this content.

[quentin5000]

Hi both

Many thanks for your replies however I cannot see how this formula would perform the first and last part of the calculation which is to find the term "Weekly Tasks" in column A and then start the count and then stop when the heading "Other Tasks" in column A is found. I will be performing the calculation over multiple worksheets and in some cases the term "Weekly Tasks" will start at different row numbers.

Thank you

[canapone]

Ciao

please refer to the attachment: the formula in K4 needs to be array entered (control+shift+enter) to return "ok" or "review"

Please Login or Register  to view this content.

Regards

[FDibbins]

Upload a more representative sample of your WB please.

[quentin5000]

Hi all

Apologies for the delay.

Thanks for the upload Canapone, your formula hit the nail on the head as it does exactly what I need it to do.

I will need to find out what the MMULT does as I have not used it before.

Thanks again to everyone else who made a contribution, its very much appreciated!