Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Totals
I am working on a spreadsheet that has weekly totals stored on it. I want to
attach a summary page that shows the sum of these totals. Is there any way I can setup the function to pick up the cells that have the weekly totals without having to select each cell individually? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Totals
Are you using Data|Subtotals to get those subtotals?
If yes, then maybe you can just keep the data where it sits and use the outlining symbols to the left to hide the details. If you really want to copy just the subtotals, you can hide the details select the range to copy edit|Goto|special Visible cells only edit|copy and paste where you want. If this doesn't help, you may want to explain more. Blade370 wrote: I am working on a spreadsheet that has weekly totals stored on it. I want to attach a summary page that shows the sum of these totals. Is there any way I can setup the function to pick up the cells that have the weekly totals without having to select each cell individually? -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Totals
Hi,
Sounds like you have the weekly totals and the daily totals in the same column. Why not put the weekly totals in a seperate column and then sum that column? In any case if you want specific answers then you'll need to provide more details. Regards! Jean-Guy "Blade370" wrote: I am working on a spreadsheet that has weekly totals stored on it. I want to attach a summary page that shows the sum of these totals. Is there any way I can setup the function to pick up the cells that have the weekly totals without having to select each cell individually? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Totals
Maybe I wasn't clear enough. I have to add up cells in a column that are 6
cells apart as these cells contain the figures I need to sum. However I do not want to have to select all of these sells individually. Is there any function I could use to simplify this? I have seen one that sums figures in a row which were 10 cells apart i.e. A10, A20, A30 etc using sumproduct((mod(row(A1:A50),10),0)*A1:A50)). Is there anything like this for columns? As my info starts at F14 and is in every sixth colum after that. "Dave Peterson" wrote Are you using Data|Subtotals to get those subtotals? If yes, then maybe you can just keep the data where it sits and use the outlining symbols to the left to hide the details. If you really want to copy just the subtotals, you can hide the details select the range to copy edit|Goto|special Visible cells only edit|copy and paste where you want. If this doesn't help, you may want to explain more. Blade370 wrote: I am working on a spreadsheet that has weekly totals stored on it. I want to attach a summary page that shows the sum of these totals. Is there any way I can setup the function to pick up the cells that have the weekly totals without having to select each cell individually? -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Totals
Maybe I wasn't clear enough. I have to add up cells in a column that are 6
cells apart as these cells contain the figures I need to sum. However I do not want to have to select all of these sells individually. Is there any function I could use to simplify this? I have seen one that sums figures in a row which were 10 cells apart i.e. A10, A20, A30 etc using sumproduct((mod(row(A1:A50),10),0)*A1:A50)). Is there anything like this for columns? As my info starts at F14 and is in every sixth colum after that. "pinmaster" wrote: Hi, Sounds like you have the weekly totals and the daily totals in the same column. Why not put the weekly totals in a seperate column and then sum that column? In any case if you want specific answers then you'll need to provide more details. Regards! Jean-Guy "Blade370" wrote: I am working on a spreadsheet that has weekly totals stored on it. I want to attach a summary page that shows the sum of these totals. Is there any way I can setup the function to pick up the cells that have the weekly totals without having to select each cell individually? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Totals
Hi,
Try something like this: =SUMPRODUCT(--(MOD(ROW(F14:F100),6)=2),F14:F100) HTH Jean-Guy "Blade370" wrote: Maybe I wasn't clear enough. I have to add up cells in a column that are 6 cells apart as these cells contain the figures I need to sum. However I do not want to have to select all of these sells individually. Is there any function I could use to simplify this? I have seen one that sums figures in a row which were 10 cells apart i.e. A10, A20, A30 etc using sumproduct((mod(row(A1:A50),10),0)*A1:A50)). Is there anything like this for columns? As my info starts at F14 and is in every sixth colum after that. "pinmaster" wrote: Hi, Sounds like you have the weekly totals and the daily totals in the same column. Why not put the weekly totals in a seperate column and then sum that column? In any case if you want specific answers then you'll need to provide more details. Regards! Jean-Guy "Blade370" wrote: I am working on a spreadsheet that has weekly totals stored on it. I want to attach a summary page that shows the sum of these totals. Is there any way I can setup the function to pick up the cells that have the weekly totals without having to select each cell individually? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Totals
Sure. You can use the same kind of formula:
=SUMPRODUCT((MOD(COLUMN(F14:IV14),6)=MOD(COLUMN(F1 4),6))*(F14:IV14)) Personally, I'd use another row as an indicator. Put an x there (or something). Then use: =sumif($f$13:$iv$13,="x",$f13:$iv13) Then I could relax--just in case someone inserted/deleted a column. ps, don't forget to include the sheet names! =SUMPRODUCT((MOD(COLUMN(Sheet1!F14:IV14),6)=MOD(CO LUMN(Sheet1!F14),6)) *(Sheet1!F14:IV14)) Blade370 wrote: Maybe I wasn't clear enough. I have to add up cells in a column that are 6 cells apart as these cells contain the figures I need to sum. However I do not want to have to select all of these sells individually. Is there any function I could use to simplify this? I have seen one that sums figures in a row which were 10 cells apart i.e. A10, A20, A30 etc using sumproduct((mod(row(A1:A50),10),0)*A1:A50)). Is there anything like this for columns? As my info starts at F14 and is in every sixth colum after that. "Dave Peterson" wrote Are you using Data|Subtotals to get those subtotals? If yes, then maybe you can just keep the data where it sits and use the outlining symbols to the left to hide the details. If you really want to copy just the subtotals, you can hide the details select the range to copy edit|Goto|special Visible cells only edit|copy and paste where you want. If this doesn't help, you may want to explain more. Blade370 wrote: I am working on a spreadsheet that has weekly totals stored on it. I want to attach a summary page that shows the sum of these totals. Is there any way I can setup the function to pick up the cells that have the weekly totals without having to select each cell individually? -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Totals
OOPS.......Guess I misread your post
=SUMPRODUCT(--(MOD(COLUMN(F14:Z14),6)=0),F14:Z14) Sorry about that! Jean-Guy "Blade370" wrote: Maybe I wasn't clear enough. I have to add up cells in a column that are 6 cells apart as these cells contain the figures I need to sum. However I do not want to have to select all of these sells individually. Is there any function I could use to simplify this? I have seen one that sums figures in a row which were 10 cells apart i.e. A10, A20, A30 etc using sumproduct((mod(row(A1:A50),10),0)*A1:A50)). Is there anything like this for columns? As my info starts at F14 and is in every sixth colum after that. "pinmaster" wrote: Hi, Sounds like you have the weekly totals and the daily totals in the same column. Why not put the weekly totals in a seperate column and then sum that column? In any case if you want specific answers then you'll need to provide more details. Regards! Jean-Guy "Blade370" wrote: I am working on a spreadsheet that has weekly totals stored on it. I want to attach a summary page that shows the sum of these totals. Is there any way I can setup the function to pick up the cells that have the weekly totals without having to select each cell individually? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing Weekly Totals into Monthly Totals | Excel Worksheet Functions | |||
sub totals | Excel Discussion (Misc queries) | |||
Totals | Excel Discussion (Misc queries) | |||
How do I sum YTD totals based on monthly totals | Excel Discussion (Misc queries) | |||
Comparing/matching totals in a column to totals in a row | Excel Worksheet Functions |