Log In
Register
Home
Active Member
December 11, 2023
Offline
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
See Answer
Moderators
January 31, 2022
Offline
No file was attached. Please try again and don't forget to press 'Start upload' (and wait a bit) before you submit your reply.
Active Member
December 11, 2023
Offline
Sorry. Here it is
Moderators
January 31, 2022
Offline
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.
Answers Post
Moderators
January 31, 2022
Offline
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))
1 Guest(s)
