LinkBack URL
About LinkBacks
Good day. I have a file that's similar to the attached. I'm using sumproduct function to add values in column "I" based on their week number of the dates in column "H". The formular is returning error. What am I not doing right please?
Good day. I have a file that's similar to the attached. I'm using sumproduct function to add values in column "I" based on their week number of the dates in column "H". The formular is returning error. What am I not doing right please?
Last edited by [email protected]; 05-14-2019 at 05:41 AM.
Try =SUMPRODUCT(--(WEEKNUM($H$3:$H$24,2)=1*RIGHT(A3,2)),$I$3:$I$24)
The RIGHT function returns text whilst WEEKNUM returns a number
Multiplyiing the RIGHT function with 1 coerces it to a real number
Last edited by Pepe Le Mokko; 05-10-2019 at 11:52 AM.
Could you use a helper column?
In column F >> =WEEKNUM(G3,2)
In column B >> =IFERROR(VLOOKUP(SUBSTITUTE(A3,"Week","")+0,$F$3:$H$24,3,0),"")
HTH
Regards, Jeff
I moved the WEEKNUM Formula to Column I. so I had Col H with the date, col I with the weeknum and col J with the 1234 and then used this SUMIFS formula.
=SUMIFS(J:J,I:I,RIGHT(A3,2)*1)
Pepe; I tried that and still got a #Value error....thoughts?
Ernest
Please consider adding a * if I helped
Nothing drives me crazy - I'm always close enough to walk....
I have tried all the suggested solutions and still get error. Maybe there's something I'm not getting right.
check out the attached....
WEEKNUM is one Excel formula that cannot accept an array as an argument, so you will definitely need a helper column. judgeh59's solution in post #4 worked for me.
If someone helped achieve your solution, consider clicking "* Add Reputation" on their post.
If your question has been answered, mark the thread as [SOLVED] using the Thread Tools menu at the top of the thread.
The inclusion of a unary plus operator is one way to coerce an array of returns from that function:Originally Posted by Melvosh
=SUMPRODUCT(--(WEEKNUM(+$H$3:$H$24,2)=0+RIGHT(A3,2)),$I$3:$I$24)
Regards
My bad.
The solution I presented works perfectly in LibreOffice.
Not so in Excel .(background use of the TYPE function for comparisons?)
XOR LX formula works very well. Thanks all.
You're welcome!
Cheers
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks