Formula Date modification by Rongzo1971

Flora Edwards
Flora Edwards used Ask the Experts™
on
I had this question after viewing modification of formula as i get another problem..

i have another challenge where my database dates are stored in a weird way. please see attached. the formula returns zero

thanks for your help as usual. much appreciated.
EE3.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Team Lead
Commented:
in F2, try this formula:

=SUMPRODUCT(B:B,--(A:A>=TEXT(DATEVALUE(LEFT(E2,8)),"YYYYMM")),--(A:A<=TEXT(DATEVALUE(RIGHT(E2,8)),"YYYYMM")))

Open in new window

Top Expert 2016
Commented:
Hi,

pls try as an array formula (Ctrl+Shitf-Enter)
=SUMPRODUCT(B:B,IFERROR(--(VALUE(A:A)>=--(TEXT(DATEVALUE(LEFT(E2,8)),"yyyyMM"))),0),IFERROR(--(VALUE(A:A)<=--(TEXT(DATEVALUE(RIGHT(E2,8)),"yyyyMM"))),0))

Open in new window

Regards

EDIT yyyy for years
Flora EdwardsMedicine

Author

Commented:
Thanks Ryan and Rgonzo.

both works fine.  i am just curious on the IFERROR used by Rgonzo.    why the IFERROR used here. what purpose does it serve?
Flora EdwardsMedicine

Author

Commented:
Thank very much.
Flora EdwardsMedicine

Author

Commented:
dear Ryan and Rgonzo.  i have posted a follow up question with sample data which i could not solve by myself.  it looks very complicated to add more condition to the sumproduct.

i really appreciate your help.   here is my question posted https://www.experts-exchange.com/questions/29068162/Formula-modification-help-needed-Rgonzo1971-Ryan-Chong-earlier-solution.html

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