Hi,
I have a SUMPRODUCT function, which needs to lookup its arguments. I have tried
This function returns #value.
Please Login or Register to view this content.
itself works if CTRL-SHIFT-ENTERed. If entered normally, the function returns also #value.Please Login or Register to view this content.
Does somebody know, if and how array (CTRL-SHIFT-ENTER) functions can be arguments of a SUMPRODUCT function?
Thanks!!
If you can sort your I7:J10 table in alphabetically ascending order by Name column.
then you can use:
=SUMPRODUCT((LOOKUP($I$20:$I$25,$I$7:$J$11)=$A2)*($J$19:$L$19=B$1),$J$20:$L$25)
copied down and across.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Getting closer, thanks!!
What if the input is not in alphabetical order?
Is it a problem to sort it?
Yes. Somebody will copy and paste several input sheets in the workbook, and the results will be calculated automaticly (nice dream, not?)
Ok, try:
=SUMPRODUCT((ISNUMBER(MATCH($I$20:$I$25&$A2,$I$7:$I$11&$J$7:$J$11,0)))*($J$19:$L$19=B$1),$J$20:$L$25)
Funny! The LOOKUP-version gives different results due to alphabetical issues, and the MATCH-version gives also different results (not the same as LOOKUP)
I got the same results in the sample you attached.
Wow, sorry. I must have made a copy and paste mistake. I love you!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks