Microsoft Excel - How do I total the values when a specified item is picked from a drop down list?

Question

I am trying to create a stock control sheet where 3 different teams use the same stock. I have created a drop down list with each team name and used a formula to work out remaining stock, what i really need is to add a running total used by each team. I have tried consolidation without success. Any help appreciated. Thanks

Answer 1

Try adding a new column for each team name, then use the if statement to show a value only for that team, then total at the bottom. So if your team name field is B2 for the item and your stock is B3, you'd have =if(B2=[team name],B3,0) across each column, with [team name] set for each possible value of your drop down. Then sum each column at the bottom to get your team totals.