I have a column B that is filed with route names from B6:B36. This is done by selecting the routes name from a list in each cell. Columns C to M represents returned goods, so the area C6:M36 is filed with numbers representing returned. A route can apear more than once in B column. How can I calculate the returned goods for each route?
I think =SUMPRODUCT(($B$6:$B436=routename)*$C$6:$M$36) should work given the sum of the returned goods.
edited: 20161121
Attached a sample file.
Hi, thank you for your answer!
Unfortunately using SUMPRODUCT returnes a “#NAME?” error. I’ve tried to put routename in quotes but no luck. I think after typing this function I have to press Shift+Alt+Enter and not just Enter, right?
SUMPRODUCT it’s an array function so no need for enter it as array…
Hi, thank you for your answer!
Unfortunately using SUMPRODUCT returnes a “#NAME?” error. I’ve tried to put routename in quotes but no luck. I think after typing this function I have to press Shift+Alt+Enter and not just Enter, right?
EDIT: It worked! I saw the example you uploaded, I don’t know what was the culprit but it is working now.
Thank you very much!