Suming cells depending on values chosen

M

mike_ate_a_pie

Board Regular
Joined
Sep 25, 2009
Messages
69
Hi all,

Sorry if this isn't explained that clearly... its a little complicated but here goes.

I have 3 individual sheets and i am trying to build a function that when certain criteria is chosen then the relevant cells are added.

I have the various options for the user:
1. A alone
2. B alone
3. C alone
3. A and B
4. A and C
5. B and C
6. A and B and C

Depending on the option chosen i want to add the the relevant information from sheet A, B and/or C.

I have tried using IF formulas but with no luck and Excel runs too slowly if i get them to work.

Has any body got any ideas about an easier more effective way to do this?

Thanks in advance,

Mike
 
Sort by date Sort by votes
This may help explain more what i am trying to do. Basicaaly add various combinations easily and quickly depending on what comination is chosen

Consolidated
 <table style="border-collapse: collapse; width: 432pt;" width="576" border="0" cellpadding="0" cellspacing="0"><col style="width: 59pt;" width="79"> <col style="width: 48pt;" width="64" span="4"> <col style="width: 133pt;" width="177"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 59pt;" width="79" height="20">Sales</td> <td class="xl64" style="width: 48pt;" width="64">
</td> <td class="xl64" style="width: 48pt;" width="64">
</td> <td class="xl65" style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 133pt;" width="177">Consolidation combination</td> <td class="xl71" style="width: 48pt;" width="64">7</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">Cos</td> <td>
</td> <td>
</td> <td class="xl67">
</td> <td>
</td> <td>
</td> <td class="xl72">
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl68" style="height: 15.75pt;" height="21">gross profit</td> <td class="xl69">
</td> <td class="xl69">
</td> <td class="xl70">
</td> <td>
</td> <td>A</td> <td class="xl71">1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>B</td> <td class="xl71">2</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">A</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>C</td> <td class="xl71">3</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Sales</td> <td align="right">10</td> <td align="right">12</td> <td align="right">14</td> <td>
</td> <td>A and B</td> <td class="xl71">4</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Cos</td> <td align="right">8</td> <td align="right">7.5</td> <td align="right">6</td> <td>
</td> <td>A and C</td> <td class="xl71">5</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">gross profit</td> <td align="right">2</td> <td align="right">4.5</td> <td align="right">8</td> <td>
</td> <td>C and B</td> <td class="xl71">6</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>A and B and C</td> <td class="xl71">7</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">B</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Sales</td> <td align="right">11</td> <td align="right">13</td> <td align="right">15</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Cos</td> <td align="right">6</td> <td align="right">5</td> <td align="right">4</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">gross profit</td> <td align="right">5</td> <td align="right">8</td> <td align="right">11</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">C</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Sales</td> <td align="right">9</td> <td align="right">11</td> <td align="right">13</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Cos</td> <td align="right">7</td> <td align="right">8</td> <td align="right">7</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">gross profit</td> <td align="right">2</td> <td align="right">3</td> <td align="right">6</td> <td>
</td> <td>
</td> <td>
</td> </tr> </tbody></table>
Any help apprciated, this would be really useful to me.

Thanks

Mike
 
Upvote 0
OK. So, if you select, say, 7, what do you want the result to be, and which numbers is the result supposed to be made up from ?
 
Upvote 0
say if i enter 7 then i want the consolidated to be. This just take the figures from A, B and C added together.
<table style="border-collapse: collapse; width: 192pt;" width="256" border="0" cellpadding="0" cellspacing="0"><tbody><tr style="height: 51.75pt;" height="69"><td class="xl63" style="height: 51.75pt; width: 48pt;" width="64" height="69">Sales</td> <td class="xl63" style="width: 48pt;" width="64" align="right">30</td> <td class="xl63" style="width: 48pt;" width="64" align="right">36</td> <td class="xl63" style="width: 48pt;" width="64" align="right">42</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 48pt;" width="64" height="20">Cos</td> <td class="xl63" style="width: 48pt;" width="64" align="right">21</td> <td class="xl63" style="width: 48pt;" width="64" align="right">20.5</td> <td class="xl63" style="width: 48pt;" width="64" align="right">17</td> </tr> <tr style="height: 26.25pt;" height="35"> <td class="xl63" style="height: 26.25pt; width: 48pt;" width="64" height="35">gross profit</td> <td class="xl63" style="width: 48pt;" width="64" align="right">9</td> <td class="xl63" style="width: 48pt;" width="64" align="right">15.5</td> <td class="xl63" style="width: 48pt;" width="64" align="right">25

</td> </tr> </tbody></table>
Another eg. if i selected 6 it would be. Again this would be the numbers form B and C added together.

<table style="border-collapse: collapse; width: 192pt;" width="256" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="4"> <tbody><tr style="height: 51.75pt;" height="69"> <td class="xl65" style="height: 51.75pt; width: 48pt;" width="64" height="69">Sales</td> <td class="xl65" style="width: 48pt;" width="64" align="right">20</td> <td class="xl65" style="width: 48pt;" width="64" align="right">24</td> <td class="xl65" style="width: 48pt;" width="64" align="right">28</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 48pt;" width="64" height="20">Cos</td> <td class="xl65" style="width: 48pt;" width="64" align="right">13</td> <td class="xl65" style="width: 48pt;" width="64" align="right">13</td> <td class="xl65" style="width: 48pt;" width="64" align="right">11</td> </tr> <tr style="height: 26.25pt;" height="35"> <td class="xl65" style="height: 26.25pt; width: 48pt;" width="64" height="35">gross profit</td> <td class="xl65" style="width: 48pt;" width="64" align="right">7</td> <td class="xl65" style="width: 48pt;" width="64" align="right">11</td> <td class="xl65" style="width: 48pt;" width="64" align="right">17</td> </tr> </tbody></table>
Hope this helps, thanks
 
Upvote 0
OK I think I understand. You can do this with basic IF functions, but I'm going to have a play around to see if I can come up with something a bit neater.
 
Upvote 0
OK. This may not be the best possible way, but it works, and the formulas are shorter, I think, then IF formulas would be.

Consider this simplified example.
Let's say the first figure under section A is in cell A2.
And the first figure under section B is in cell A6.
And the first figure under section C is in cell A10.
I know they're not like this, you'll need to adapt this.

Then, set up a little array like this, which captures all the options.
Let's say you put it in the range E1:H8.
Option..A..B..C
1.........1..0..0
2.........0..1..0
3.........0..0..1
4.........1..1..0
5.........1..0..1
6.........0..1..1
7.........1..1..1

And let's say you plug in the option you want into cell I3.

Code: 
=A2*VLOOKUP($I$2,$E$2:$H$8,2,FALSE)+
A6*VLOOKUP($I$2,$E$2:$H$8,3,FALSE)+
A10*VLOOKUP($I$2,$E$2:$H$8,4,FALSE)

This will sum the data, depending on which option you have chosen.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Add to Data Validation Values From Data Entry Cell
Replies
4
Views
401
kvsrinivasamurthy
K
J
  • Question Question
Dynamic Email Generation via VBA - Extracting & Sending Data Across Multiple Sheets
Replies
0
Views
133
Jess1234556
J
O
  • Solved
How to approach the need for "nested" dropdowns (2nd dropdown depends on 1st dropdown value selected)
Replies
7
Views
47
dreid1011
dreid1011
N
  • Question Question
VBA to add title from formula to a save as name
Replies
0
Views
230
NKUNZI
N
M
  • Question Question
Creating a form based on cell values and predetermined layouts
Replies
3
Views
73
mummbles74
M

Forum statistics

Threads
1,226,907
Messages
6,193,601
Members
453,810
Latest member
Gks77117

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