Dropdown Selection & Calculations

nymyth

Board Regular
Joined
Mar 4, 2010
Messages
104
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.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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
 
Last edited:
Upvote 0
Thanks Jeffery, this pointed me in the right direction. Had to make some tweaks to the SUMIFs, but it works like a charm.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top