Hi all,
I have problem with sumproduct please help me, I'm really stuck..
My data is attached. I want to search coal ABC sum value based on the date on B5.
My attempt: =SUMPRODUCT(($L$4:$L$13),($K$4:$K$13="ABC")*(MONTH($M$4:$M$13=B3))*(YEAR($M$4:$M$13=B4)*($M$4:$M$13<>"")))---> failed.
Thanks in advance guys!
You are missing a closing parenthesis in MONTH and YEAR. Try this:Formula:
=SUMPRODUCT(($L$4:$L$13),($K$4:$K$13="ABC")*(MONTH($M$4:$M$13)=B3)*(YEAR($M$4:$M$13)=B4)*($M$4:$M$13<>""))
Not sure but try this :
=SUMPRODUCT(($K$4:$K$13="ABC")*(DATE(YEAR($M$4:$M$13),MONTH($M$4:$M$13),1)=DATE(YEAR(B$5),MONTH(B$5),1))*($L$4:$L$13))
welcome to the forum, bibbi. you have the brackets at the wrong places. try:
Formula:=SUMPRODUCT($L$4:$L$13,($K$4:$K$13="ABC")*(MONTH($M$4:$M$13)=B3)*(YEAR($M$4:$M$13)=B4)*($M$4:$M$13<>""))
and since you are checking for month in column M, you dont have to check if column M is blank.
Formula:=SUMPRODUCT($L$4:$L$13,($K$4:$K$13="ABC")*(MONTH($M$4:$M$13)=B3)*(YEAR($M$4:$M$13)=B4))
alternatively without MONTH & YEAR:
=SUMPRODUCT($L$4:$L$13,($K$4:$K$13="ABC")*(TEXT($M$4:$M$13,"yyymm")=TEXT(B5,"yyymm")))
or even faster with Excel 2007 & above:
=SUMIFS($L:$L,$K:$K,"ABC",$M:$M,">="&B5,$M:$M,"<="&EOMONTH(B5,0))
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
Thank you all for the very quick replies, azumi, flameretired, and benishiryo!
It solved my problem hehe.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks