Formula modification help needed, Rgonzo1971 & Ryan Chong earlier solution

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
LVL 6
FloraAsked:
Who is Participating?
 
Rgonzo1971Connect With a Mentor 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
1
 
FloraAuthor 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.
0
 
FloraAuthor 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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.