| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
count count multiple rows
I have a spreadsheet with 2318 rows dates ranging from 2003 through to 2008 -
I would like to have the sheet split at the end of each year and count how many rows are in each section - can this be done please -- Thanks Lise |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
count count multiple rows
Dear Lise;
Why dont you make a Pivot table . you can make it yearwise and you can count entries.it would be more useful then applying formulas in same sheet. if you don't know send me your sheet. i will make a pivot and send you a vedio also. "Lise" wrote: I have a spreadsheet with 2318 rows dates ranging from 2003 through to 2008 - I would like to have the sheet split at the end of each year and count how many rows are in each section - can this be done please -- Thanks Lise |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
count count multiple rows
You can use the Data-Subtotals feature. Select the table and headers and
choose "at each change in YEAR" to Count the rows and put the subtotal below the data. "Lise" wrote: I have a spreadsheet with 2318 rows dates ranging from 2003 through to 2008 - I would like to have the sheet split at the end of each year and count how many rows are in each section - can this be done please -- Thanks Lise |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
count count multiple rows
Thanks for both your replies and yourkind offer Satyendra but I think I may
go with Rosa's idea with one extra question please - when I do that Rosa the dates split at each day ie" all listed for 16th Sep etc - I want to actually have it split at the end of each year only what would you suggest I change?? -- Thanks Lise "Rosa59" wrote: You can use the Data-Subtotals feature. Select the table and headers and choose "at each change in YEAR" to Count the rows and put the subtotal below the data. "Lise" wrote: I have a spreadsheet with 2318 rows dates ranging from 2003 through to 2008 - I would like to have the sheet split at the end of each year and count how many rows are in each section - can this be done please -- Thanks Lise |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
count count multiple rows
Glad I can help.
You need to be sure that year is in a separate column by itself, if it is not already. You don't need to change the whole date column but you need to add YEAR and write the year. You can either do it manually by dragging down to the right row, or you can use a date function called YEAR that looks at the column you specify and then writes just the year that appeared in that column. (later you can hide the column). Needless to say, then you only have to have the year column sorted in order. You'll see in subtotals, too, that it creates a ruler on the left that lets you close all the rows except just the "answers" themselves. So you can look at just the subototals. Hope this answers your question. "Lise" wrote: Thanks for both your replies and yourkind offer Satyendra but I think I may go with Rosa's idea with one extra question please - when I do that Rosa the dates split at each day ie" all listed for 16th Sep etc - I want to actually have it split at the end of each year only what would you suggest I change?? -- Thanks Lise "Rosa59" wrote: You can use the Data-Subtotals feature. Select the table and headers and choose "at each change in YEAR" to Count the rows and put the subtotal below the data. "Lise" wrote: I have a spreadsheet with 2318 rows dates ranging from 2003 through to 2008 - I would like to have the sheet split at the end of each year and count how many rows are in each section - can this be done please -- Thanks Lise |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
count count multiple rows
Fabulous Rosa - Thanks very much :-)
-- Thanks Lise "Rosa59" wrote: Glad I can help. You need to be sure that year is in a separate column by itself, if it is not already. You don't need to change the whole date column but you need to add YEAR and write the year. You can either do it manually by dragging down to the right row, or you can use a date function called YEAR that looks at the column you specify and then writes just the year that appeared in that column. (later you can hide the column). Needless to say, then you only have to have the year column sorted in order. You'll see in subtotals, too, that it creates a ruler on the left that lets you close all the rows except just the "answers" themselves. So you can look at just the subototals. Hope this answers your question. "Lise" wrote: Thanks for both your replies and yourkind offer Satyendra but I think I may go with Rosa's idea with one extra question please - when I do that Rosa the dates split at each day ie" all listed for 16th Sep etc - I want to actually have it split at the end of each year only what would you suggest I change?? -- Thanks Lise "Rosa59" wrote: You can use the Data-Subtotals feature. Select the table and headers and choose "at each change in YEAR" to Count the rows and put the subtotal below the data. "Lise" wrote: I have a spreadsheet with 2318 rows dates ranging from 2003 through to 2008 - I would like to have the sheet split at the end of each year and count how many rows are in each section - can this be done please -- Thanks Lise |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count if in multiple rows / columns | Excel Discussion (Misc queries) | |||
| Multiple Criteria, Count If, Sum Product to get count across range | Excel Worksheet Functions | |||
| Count rows and insert number to count them. | Excel Discussion (Misc queries) | |||
| Count on multiple values with duplicate rows | Excel Worksheet Functions | |||
| Count rows based on multiple criteria | Excel Worksheet Functions | |||
Linear Mode
