| x404.co.uk http://www.x404.co.uk/forum/ |
|
| Excel brain ache [SOLVED] http://www.x404.co.uk/forum/viewtopic.php?f=3&t=11100 |
Page 1 of 1 |
| Author: | Spreadie [ Thu Oct 21, 2010 10:48 am ] |
| Post subject: | Excel brain ache [SOLVED] |
No doubt someone here will have an answer for this one.. I have a table with hundreds of customers in column A and their sales volumes per month in the adjoining 10 columns. I have a second table with around 80 customers and their sales volume of a specific product stream in the adjoining 10 columns. I want excel to show the sales volume for each customer listed in the second table minus the volume for the specific product stream. I need to show this for Jan through Oct for each of the 80. Does that make sense? I thought I could do it with vlookup, but tied myself in a knot. |
|
| Author: | jonlumb [ Thu Oct 21, 2010 10:51 am ] |
| Post subject: | Re: Excel brain ache |
Can you give us the specifics of the layouts, I'm not quite sure what you're wanting to remove from which other figure? |
|
| Author: | Spreadie [ Thu Oct 21, 2010 11:30 am ] |
| Post subject: | Re: Excel brain ache |
Sorry, work FW won't let me access photobucket to post a pic, and trying to post a table in here looks like hell. The 80 customers in the second table are also listed in table one, but table one shows their overall sales. I still want to show overall monthly sales in table one but, for those 80 customers I want to subtract the sales volumes in table two from the total sales for each month in table one. |
|
| Author: | RichardLucas [ Thu Oct 21, 2010 11:34 am ] |
| Post subject: | Re: Excel brain ache |
Assuming that there are 2 tabs (one for all sales and one for product sales) and that column A has the customer name and then columns b-k have the sales figures in both cases, I think the following formula would work.. =SUMIF(All!A2:A9, A3, All!B2:B9)-SUMIF(Product!A2:A9, A3, Prod_A!B2:B9) Where column A on the new table also has the customer name. Change B to C-K for each month. Hope this makes sense (and helps). |
|
| Author: | Spreadie [ Thu Oct 21, 2010 12:57 pm ] |
| Post subject: | Re: Excel brain ache |
Thanks Richard, That works for the first few entries then fails for some reason, I'll have a play around with it. [edit] just had to lock a few cell references and it worked fine. Thanks again |
|
| Author: | davrosG5 [ Thu Oct 21, 2010 11:29 pm ] |
| Post subject: | Re: Excel brain ache [SOLVED] |
I love it when a formula cones together. |
|
| Page 1 of 1 | All times are UTC |
| Powered by phpBB® Forum Software © phpBB Group https://www.phpbb.com/ |
|