Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotals every "X" number of rows
Is there a quick, easy way in Excel to do subtotals ever certain number of
rows? In my case I need subtotals every 15 rows. Oh, this is Excel 2003. I know how to use the Subtotals tool but the only option there seems to be subtotals based on the change in values of a column. In my case I don't care about the values of any columns, I just need the subtotals every 15 rows. Of course I could do this manually, but I'm hoping there's a quick and easy way to do it similar to how the built-in Subtotals tool works. Thank you. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotals every "X" number of rows
In a helper column you could put this formula on row 1:
=INT(ROW()/15) and copy it down. It will give you 15 zeros, followed by 15 ones, then 15 twos etc, so you could use the Data | Subtotals feature using this column to determine where the subtotals should go (i.e. for each change in this column ...). Hope this helps. Pete On Dec 10, 4:34*pm, clh wrote: Is there a quick, easy way in Excel to do subtotals ever certain number of rows? *In my case I need subtotals every 15 rows. *Oh, this is Excel 2003. I know how to use the Subtotals tool but the only option there seems to be subtotals based on the change in values of a column. *In my case I don't care about the values of any columns, I just need the subtotals every 15 rows. Of course I could do this manually, but I'm hoping there's a quick and easy way to do it similar to how the built-in Subtotals tool works. Thank you. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotals every "X" number of rows
Sorry, it should really be:
=INT((ROW()-1)/15) to give you 15 zeros from row 1. Pete On Dec 10, 4:34*pm, clh wrote: Is there a quick, easy way in Excel to do subtotals ever certain number of rows? *In my case I need subtotals every 15 rows. *Oh, this is Excel 2003. I know how to use the Subtotals tool but the only option there seems to be subtotals based on the change in values of a column. *In my case I don't care about the values of any columns, I just need the subtotals every 15 rows. Of course I could do this manually, but I'm hoping there's a quick and easy way to do it similar to how the built-in Subtotals tool works. Thank you. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotals every "X" number of rows
One way is with a helper column ;
Enter this in a helper Col (say Col B) =MOD(Row(),15) and copy down Then you can use the following to SUM the value in Col A for every 15th row =SUMPRODUCT(A1:A25,--(B1:B25=1)) "clh" wrote: Is there a quick, easy way in Excel to do subtotals ever certain number of rows? In my case I need subtotals every 15 rows. Oh, this is Excel 2003. I know how to use the Subtotals tool but the only option there seems to be subtotals based on the change in values of a column. In my case I don't care about the values of any columns, I just need the subtotals every 15 rows. Of course I could do this manually, but I'm hoping there's a quick and easy way to do it similar to how the built-in Subtotals tool works. Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
how i convert "100" to "hundred"( number to text) in excel-2007 | Excel Worksheet Functions | |||
HLOOKUP copied across rows with "row index number" changing | Excel Worksheet Functions | |||
How to Count the number of "rows" (or Array items) included in a Sumif formula? | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |