Calcultion with variable

Hi All,

Yet another challenge for me.

Sample sheet attached which I hope helps you understand

Thanks for any help offered.

Hi Carim

Almost there but I dont want a sum, just a value of 1 returned if a "y" exists anywhere in the range
with of course the limitations of NA being present in H17 and or I17

Updated sheet attached with a few options to show the variables and expected results.

Thanks

Hi Carim

Justthe job, great work.

Quote from Carim

You seem to be surrounded by many challenges ...!!!

I think this is due to age, but cannot be sure :cheers:

Many thanks

Hi Carim,

In my haste to get a result, I omitted a vital criteria from this calculation.

"y" must be present over the whole range Act 1, Act 2, Act 3 as a minimum but can include Act 4 and Act 5 if they are active.

In the example I have inclued a sum column to show my thought process, but I cannot include this on the actual worksheet (no columns between the data sets)
But if it is required, I could change a few things around.

See attached which hopefully exaplains

Thanks

Hi Carim

Thanks for the assistance "Again"

Almost there, but I do not want a sum of the row, just a 1 in the left column if all the criteria is acheived.

Thanks

Hi Carim,

Sorry I have really confused you now.

Attached is an updated sampe to hopefully explain a little more.

Thanks

for further clarity, and if its simpler, the calculations in column D do not need to be for each row.
This can be a single calculation in D18 to show achievement of all the criteria.

Thanks

Hi Carim

Your time and efforts are very much appreciated
It is indeed the last version as everything works great.

I just need to incorporate the extra 2 columns into the worksheet now.
Should be just a case of moving stuff around, but array formulas can be awkward.

Thank you

Hi Carim

Thats great, thank you for the follow up.
I modified the solution slightly as the same columns in the sample sheet are replicated accross the columns many times but with 1 column spacing between.
so, the rows and columns calculations have moved, but nevertheless an awesome solution

btw, that is some Array formula, and I have no idea what is means :duh:

Amazing support once again.

Thank you