how to sum an area of numbers applying two conditions?

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 close merge delete

Sort by » oldest newest most voted

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

more

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

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.

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

more