Formula modification help needed, Rgonzo1971 & Ryan Chong earlier solution

Flora Edwards
Flora Edwards used Ask the Experts™
on
I had this question after viewing Formula Date modification by Rongzo1971.

please see attached file. this one looks a bit challenging. i tried a lot, could not figure out to add additional condition in SUMPRODUCT for second column date.

i need the formula in the yellow cells.

very much appreciate your help.
EE4.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
Hi,

pls try in F2
=SUMPRODUCT(C:C,--(A:A>=DATEVALUE(LEFT(E2,8))),--(A:A<=DATEVALUE(RIGHT(E2,8))),--(B:B<(DATEVALUE(RIGHT(E2,8))+15)))

Open in new window

and in F3 and down
=SUMPRODUCT(C:C,--(A:A>=DATEVALUE(LEFT(E3,8))),--(A:A<=DATEVALUE(RIGHT(E3,8))),--(B:B<(DATEVALUE(RIGHT(E3,8))+15)))+
SUMPRODUCT(C:C,--(B:B>=DATEVALUE(LEFT(E3,8))),--(B:B<=DATEVALUE(RIGHT(E3,8))),--(B:B>=(DATEVALUE(LEFTT(E3,8))+15-1)))

Open in new window

Regards
Flora EdwardsMedicine

Author

Commented:
This is one of the most sophisticated problem that is solved by you Rgonzo1971.  you are genius! you must have a big brain.  this was not easy. thanks a million.

there was a typo in the F3 formula  where at the end it had a double TT for the Left and i found it and fixed it.  but your formula really amazed me. i could not decode it how it works.  but it does the job very well.  

is it possible to merge both of two different formulas together as one? like if i just paste one formula in F2 and drag down?  if it is too much, then i can live with the one you already helped me with. it is already amazing !  i love it.
Flora EdwardsMedicine

Author

Commented:
dear Rognzo1971,

there seems to be a problem with the formula, if i add new date range.

i have uploaded the document. i have also posted new question for this.

I would really appreciate your help.  thanks.  the total is 90773 while your formula now results 91855

link to the new question i posted on this issue is here https://www.experts-exchange.com/questions/29068462/Formula-modification-help-needed-Rgonzo1971-earlier-solution.html
EE-4.xlsx

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial