Formula

  • Thread starter Thread starter Anonymous
  • Start date Start date
A

Anonymous

Please help! I have been trying to get an answer but
nobody seems able to help me.

I have a drop down on each rows which contains 8 different
options. These options have each a grand Total. I want
the formula to say that if option 1 is selected than the
amount (next to it, in next column) will be deducted from
Grand Total # 1. Same for all the other option. The
problem with this is I have 100 entries and I want it to
recognized that all option 1 will be deducted from grand
total 1. All option 2 will be deducted from Grand Total 2
and so on.

I badly need an answer to my problem as time is running
out.

Thanks a bunch
 
Create a separate table to house the cost of each option (maybe on different sheet). Next to the selection option add a column titled "Cost", use "VLOOKUP" to retrieve the cost number from that table. In a separate location, create a summary table to house the budget, the total cost and the unused allocation for each option. For the total cost of each option, there is the array SUM function you can use to calculate the sum of each option, the syntax goes like this:

=SUM((Option Column = "Option 1")*Cost Column)
Since this is an array you must hit Ctrl+Shift+Enter keys instead of just Enter
 
Back
Top