Ask Your Question
0

how to sum an area of numbers applying two conditions?

asked 2016-12-01 11:31:05 +0200

Erkhamion gravatar image

Hi there! 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 sum of each item for a specific route? I managed to calculate the returned goods for each route, using SUMPRODUCT (thank you again m.a.riosv !). I'm thinking a combination of SUMRODUCT and IF but I can't figure out the syntax...

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2016-12-01 12:12:20 +0200

Lupp gravatar image

Do you mean something like =SUMPRODUCT($A$6:$A$36;$B$6:$B$36=SpecificRoute;$J$6:$J$36=SpecificGood)?

edit flag offensive delete link more

Comments

MORE DETAILS: after repositioning and adding data, b6:b36 contains routes, d3:n3 contains item labels and d6:n36 contains data (item quantities). adapting your formula to this, I get a #VALUE error

Erkhamion gravatar imageErkhamion ( 2016-12-01 14:49:57 +0200 )edit

Would you mind to also explicitly explain what actually should be added (A6:A36 I supposed) and what to do with the "item quantities".?
What shpuld be the "two conditioins" you talked of exactly.
As your "karma" is already 3 you should also be entitles to attach an example file. This would probab ly help best to avoid misunderstandings.

Lupp gravatar imageLupp ( 2016-12-01 15:35:44 +0200 )edit
0

answered 2016-12-01 16:06:55 +0200

Erkhamion gravatar image

updated 2016-12-01 16:18:29 +0200

I figured it finally out! this works for my case:

=SUMPRODUCT((B6:B36="route")*( D3:N3="item")*( D6:N36))

Thank you for your time Lupp!

EDIT: I just saw your post Lupp.This is an example file. Route names are in Greek so it will be "Greek to you"... :) I want to determine which item has the most returns per route in a months period and eventually yearly I hope I make sense given the fact English is not my native language...

example.ods

edit flag offensive delete link more

Comments

Ok. I can "read" Greek without understanding it.
Concerning ΦΟΙΝΙΚΑΣ: Is this a location on the island of Σύρος? Is the name related to the Phoenicians or to palms or ...?
In German we use the words "Das kommt mir Spanisch vor." (It sounds like Spanish to me.)

Lupp gravatar imageLupp ( 2016-12-03 15:16:17 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-12-01 11:31:05 +0200

Seen: 63 times

Last updated: Dec 01 '16