Hello! In the attached i am trying to find the overlap in T8 but i think the number i get is wrong. It should be 21(?). My thought is that blank cells F8&G8 cause problem,L8 is in the range of J7 and L8 starts earlier than L7. All these i cannot solve. Any insight would be helpful! I need to build a formula that will be pasted to all below and count common days within the ranges regardless of starting date ,ignoring blank cells and if a date is also included in another range, count only the first found and ignore the rest. I hope my description is clear! Also, if you think my method is wrong, please share your advice!
Thnx!
N
No file was attached. Please try again and don't forget to press 'Start upload' (and wait a bit) before you submit your reply.
Sorry. Here it is
Thanks! I didn't see your reply until know, although I have been on-line every day the last couple of days. Who knows why?
Anyway, you have already a lot of intelligence in columns V and onwards. Why not use that information to see where there is an overlap? For example, if both rows 7 and 8 contain 1 then there is an overlap. I entered a formula that checks just that in row 6. Then the total overlap (T8) equals the sum of V6:OG6.
See attached.
All you need to do is set the format of these cells that they seem to be blank (custom format or font color). Copy V6:OG6 to each of the sub-header rows (9, 12, 15 etc.) and copy T8 down to T11, T14 etc.
Let me know this it works for you.
Just in case you are using a modern Excel version, the formula below does all in one go, without the need of the 'helper formulas' in rows 6, 9, 12 etc.
In T8:
=SUM(--(IFERROR(--BYCOL(V7:OG8,LAMBDA(c, XOR(c))),1)=0))
Wow thnx! It was that simple after all!! THANK YOU! 🙂
