| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 34891 | 2003-06-27 01:03:00 | Excel Formula to subtotal numbers | Muzz (1286) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 155516 | 2003-06-27 01:03:00 | I have an Excell sheet with amounts in rows representing a monthly amount (under monthly headings April to March) I want to increment these numbers so that they subtotal ie April = April, May = April & May, June = April, May & June. Can anyone suggest a formula to do this? |
Muzz (1286) | ||
| 155517 | 2003-06-27 02:39:00 | Give me some cell addresses for the ranges and I might be able to whip something up. Are the months in columns across the page? If they were, say B, C, D,E, then you use the old sum formula with a half relative and half absolute address: =sum($b3:b3) If you copied this to the next cell to the right it would read: =sum($b3:c3) And as you copied the formula to the right you would get year to date totals (lots of total columns, though). Let me know if I am way off beam. robo. |
robo (205) | ||
| 155518 | 2003-06-27 02:41:00 | I don't quite understand what you want, but if you had separate cells for each month (put them anywhere out of the way you like) you could then just refer to them for the sort of totals you need made up of cell refs. | jockT (3500) | ||
| 155519 | 2003-06-27 04:41:00 | Thanks robo but that didn't seem to work..I had tried that one. All the months are listed in a row starting at B2 and they have say eg $10 under each month starting at B3. I want to end up with $10 under April, $20 under May, $30 under June etc etc right up to $120 under March in M3 position. Trust this explains it better. |
Muzz (1286) | ||
| 155520 | 2003-06-27 04:46:00 | Hi, this is easy. Just select a cell in the data then from the menu select Data-Subtotals. A menu will appear and you just select which column you want subtotals on. hth |
parry (27) | ||
| 155521 | 2003-06-27 05:03:00 | I should add that you should insert another column and use the Month formula to determine the month of the date then use this column in the "At each change in" option to obtain the subtotlals data. | parry (27) | ||
| 155522 | 2003-06-27 05:42:00 | Is it the same amount every month? I am a little confused. You say the months are in rows (I think you mean columns, if M3 is March, that is the 12th column after B for April. The values are in row 3? Where do you want the cumulative figures? robo. |
robo (205) | ||
| 155523 | 2003-06-27 05:43:00 | second thoughts, email me the worksheet (with dummy data if you want) to my official idg address (it's around here somewhere) and I can take a look and send it back. robo. |
robo (205) | ||
| 155524 | 2003-06-29 02:28:00 | Muzz, boy you take me back to the 1970's and Lotus 123, Symphony 1. You seem to be talking Budgeting ?. We used to use Macros to do the adding one month to next, to accumulate as you go. The last thing was the stacked worksheets Lotus 123 had a max. of 256 to build on. Depending on costs the most I had was 4 spreadsheets one behind the other and you Linked these to the Year to Date main spreadsheet in front. Some firms use the 4,4,5 week for budgeting, a "prick" of a system. A later post on Excel would probably be the best solution, although it seems to refer to a fixed cost each cell then building up with GST and Freight. Some things change for the better !!. Les3 |
leshibbard (873) | ||
| 155525 | 2003-06-29 03:09:00 | Yeah most Excel formulae are quite easy to supply but without actually seeing the data and how it is set out it becomes complicated :D | HadO (796) | ||
| 1 2 | |||||