Dropdown Selection & Calculations

[nymyth]

Good morning/afternoon everyone,

I am in need of some assistance. Say I have a data tab where column A is a list of 100 stores, while column B is 'items sold'. In a second tab I have 5 cells in Column A that are dropdowns from the 100 stores, the 6th cell is "All Other". In column B is a vlookup to pull the 'items sold' amount depending on the selections in the 5 cells. What I am trying to do is pull the remaining 'items sold' in the 6th cell (All Other). This is a tricky as the selection of the 5 cells can vary and therefore the "All Other" cell will differ. Is there anyone that can help point me to a possible resolve?

Thank you.

 

[Jeffrey Mahoney]

To get the items sold for each store
=SUMIFS(Sheet4!$B$2:$B$11,Sheet4!$A$2:$A$11,Sheet5!A2)

To get the Other result:
=SUM(Sheet4!B2:B11)-SUM(Sheet5!B2:B6)


Sheet4 is where you have all your data
Sheet5 is the results

Jeff

 

[nymyth]

Thanks Jeffery, this pointed me in the right direction. Had to make some tweaks to the SUMIFs, but it works like a charm.