@daddylonglegs
Thank you very much for this formula, it works perfect!!
I don't understand how SUMPRODUCT works even after pressing F1 for help but it does the job.
Could I use this formula if I had a master spreadsheet to see if my holiday dates clashed with a colleague?
Best Regards.
Michael
-----------------------------------
Windows Vista, Microsoft Office 2007
You may need a different approach. The above allows you to check a single range against a list. Presumably to do what you suggest you might want to check multiple ranges (your holiday dates) against another set of multiple ranges (a colleagues holiday dates)?Originally Posted by Zyphon
Yes I was thinking along those lines. Maybe have say a master spreadsheet that would have all my colleagues holiday dates and to check against those ranges. Although at this moment in time I have no idea of how to achieve this as it sounds quite complex.
=SUMPRODUCT((Z1:Z10>=A2)*(Z1:Z10<=B2)) works because TRUE is 1 and FALSE is 0.
Thus (Z1:Z10>=A2) gives an array of ten values which can be viewed either as TRUE or FALSE or as 1 or 0.
similarly for (Z1:Z10>=B2)
Now 1*1=1, 1*0=0, 0*1=0, 0*0=0.
So you only get a 1 when (Z1:Z10>=A2) AND (Z1:Z10>=B2) everywhere else you get 0.
Add them up and there's your answer.
PS
=SUMPRODUCT((Z1:Z10>=A2),(Z1:Z10<=B2))
should also work and can be readily extended :
=SUMPRODUCT((Z1:Z10>=A2),(Z1:Z10<=B2),(Z1:Z10<=C2)...
Mark.
Hi Mark,
Thanks for your help and explanation.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks