Ask Your Question
0

how to calc sum of data in an area that corresponts to labels in a column? [closed]

asked 2016-11-20 20:06:45 +0100

Erkhamion gravatar image

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?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-09-15 21:54:59.549552

2 Answers

Sort by » oldest newest most voted
0

answered 2016-11-20 23:21:54 +0100

m.a.riosv gravatar image

updated 2016-11-21 22:33:08 +0100

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.

SUMPRODUCT_Sample.ods

edit flag offensive delete link more

Comments

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?

Erkhamion gravatar imageErkhamion ( 2016-11-21 11:01:06 +0100 )edit

SUMPRODUCT it's an array function so no need for enter it as array..

m.a.riosv gravatar imagem.a.riosv ( 2016-11-21 22:29:19 +0100 )edit
0

answered 2016-11-21 11:04:36 +0100

Erkhamion gravatar image

updated 2016-11-22 19:26:58 +0100

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!

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2016-11-20 20:06:45 +0100

Seen: 180 times

Last updated: Nov 22 '16