Modifying complex formula: Adding a condition

[spydey]

A while back, I received some phenomenal help from @Nishant94 regarding a complex formula I was trying to build to capture unique number of overlap days based upon some criteria.

The original thread is here:

Count Number of Unique Overlap Days - Meeting Criteria

The formula (CSE) is this:

{=SUM(--(MMULT((ROW(INDIRECT(B2&":"&C2))>=TRANSPOSE(IF(Events!$A$2:$A$19=A2,Events!$C$2:$C$19)))*(ROW(INDIRECT(B2&":"&C2))<=TRANSPOSE(IF(Events!$A$2:$A$19=A2,Events!$D$2:$D$19))),Events!$C$2:$C$19^0)>0))}

The formula Nishant94 provided me works great.

However, now I need to modify it slightly to account for one additional criteria. I would think it should be somewhat simple based upon the criteria, but am not 100% sure I know where I should input the additional criteria in the already existing formula.

I have added an additional column (column E) to my sheet 2 (see original post) which contains either "Yes" or "No".

I only want to count the unique number of days, which the formula above does, but only for those rows where the new column has a YES in it.

I was thinking of something like:

{=SUM(--(MMULT((ROW(INDIRECT(B2&":"&C2))>=TRANSPOSE(IF(Events!$E$2:$E$19="Yes",IF(Events!$A$2:$A$19=A2,Events!$C$2:$C$19))))*(ROW(INDIRECT(B2&":"&C2))<=TRANSPOSE(IF(Events!$E$2:$E$19="Yes",IF(Events!$A$2:$A$19=A2,Events!$D$2:$D$19)))),Events!$C$2:$C$19^0)>0))}


I haven't tested it yet (going to in just a moment), but was hoping to get someone's input on if it should theoretically work or if I have my order and syntax perhaps incorrect. I am not very experienced with the MMULT function so still struggle to ensure it is aligned correctly.

Anythoughts, ideas, corrections, input, is greatly appreciated!!

-Spydey

 

[spydey]

Actually, now that I think about it, I think that I need my formula to be the following:

{=SUM(--(MMULT((ROW(INDIRECT(B2&":"&C2))>=TRANSPOSE(IF(Events!$A$2:$A$19=A2,IF(Events!$E$2:$E$19="Yes",Events!$C$2:$C$19))))*(ROW(INDIRECT(B2&":"&C2))<=TRANSPOSE(IF(Events!$A$2:$A$19=A2,IF(Events!$E$2:$E$19="Yes",Events!$D$2:$D$19)))),Events!$C$2:$C$19^0)>0))}

That way it identifies the item first, if it is present, then if it has a Yes.

Giving this a try.

-Spydey

 

[spydey]

Yup, that it worked!!!!

If any of you see something that maybe I missed, please let me know.

-Spydey