|
Summing Data by Date
I would like to summarise data by date for example
A1 B1 - Date, Value 1/2/9 10 1/2/9 12 2/2/9 34 3/2/9 27 4/2/9 56 with results in another range of cells: 1/2/9 22 2/2/9 34 3/2/9 27 4/2/9 56 |
Summing Data by Date
Put headers for your data. Use advanced filter to generate unique records to
say Col D DataFilterAdvanced FilterSelect Copy to another location..Select the list range, Mention 'copy to' as (say cell D1). Check "Unique Records only'..OK In E2 use the below formula and copy that down =SUMIF(A:A,D2,B:B) If this post helps click Yes --------------- Jacob Skaria "Nigel P" wrote: I would like to summarise data by date for example A1 B1 - Date, Value 1/2/9 10 1/2/9 12 2/2/9 34 3/2/9 27 4/2/9 56 with results in another range of cells: 1/2/9 22 2/2/9 34 3/2/9 27 4/2/9 56 |
Summing Data by Date
Forgot to mention to selection the date column only
DataFilterAdvanced FilterSelect Copy to another location..Select the date column... If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Put headers for your data. Use advanced filter to generate unique records to say Col D DataFilterAdvanced FilterSelect Copy to another location..Select the list range, Mention 'copy to' as (say cell D1). Check "Unique Records only'..OK In E2 use the below formula and copy that down =SUMIF(A:A,D2,B:B) If this post helps click Yes --------------- Jacob Skaria "Nigel P" wrote: I would like to summarise data by date for example A1 B1 - Date, Value 1/2/9 10 1/2/9 12 2/2/9 34 3/2/9 27 4/2/9 56 with results in another range of cells: 1/2/9 22 2/2/9 34 3/2/9 27 4/2/9 56 |
Summing Data by Date
"Jacob Skaria" wrote: Forgot to mention to selection the date column only DataFilterAdvanced FilterSelect Copy to another location..Select the date column... If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Put headers for your data. Use advanced filter to generate unique records to say Col D DataFilterAdvanced FilterSelect Copy to another location..Select the list range, Mention 'copy to' as (say cell D1). Check "Unique Records only'..OK In E2 use the below formula and copy that down =SUMIF(A:A,D2,B:B) If this post helps click Yes --------------- Jacob Skaria "Nigel P" wrote: I would like to summarise data by date for example A1 B1 - Date, Value 1/2/9 10 1/2/9 12 2/2/9 34 3/2/9 27 4/2/9 56 with results in another range of cells: 1/2/9 22 2/2/9 34 3/2/9 27 4/2/9 56 Hi thanks for the help. It gives the desired result. One small question though, is it possible to keep adding values in the colums A and B after performing the Advanced filter copy, and see the new data appear in the summary columns automatically? I want to give this spreadsheet to a friend to continue to add data too, and would not want to ask the person to do Advanced filter copy. thanks |
Summing Data by Date
Nigel
Try the below formulas in Col D In D2 =SMALL(A2:A1000,1) In D3 and the below cells use the below formula. Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" (in one line). =IF(OR(MAX($A$2:$A$1000)=D2,D2=""),"",MIN(IF($A$2: $A$1000D2,$A$2:$A$1000))) In Col E use the previous formula itself If this post helps click Yes --------------- Jacob Skaria "Nigel P" wrote: "Jacob Skaria" wrote: Forgot to mention to selection the date column only DataFilterAdvanced FilterSelect Copy to another location..Select the date column... If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Put headers for your data. Use advanced filter to generate unique records to say Col D DataFilterAdvanced FilterSelect Copy to another location..Select the list range, Mention 'copy to' as (say cell D1). Check "Unique Records only'..OK In E2 use the below formula and copy that down =SUMIF(A:A,D2,B:B) If this post helps click Yes --------------- Jacob Skaria "Nigel P" wrote: I would like to summarise data by date for example A1 B1 - Date, Value 1/2/9 10 1/2/9 12 2/2/9 34 3/2/9 27 4/2/9 56 with results in another range of cells: 1/2/9 22 2/2/9 34 3/2/9 27 4/2/9 56 Hi thanks for the help. It gives the desired result. One small question though, is it possible to keep adding values in the colums A and B after performing the Advanced filter copy, and see the new data appear in the summary columns automatically? I want to give this spreadsheet to a friend to continue to add data too, and would not want to ask the person to do Advanced filter copy. thanks |
Summing Data by Date
Nigel
Forgot to mention to format Column D as Date.. If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Nigel Try the below formulas in Col D In D2 =SMALL(A2:A1000,1) In D3 and the below cells use the below formula. Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" (in one line). =IF(OR(MAX($A$2:$A$1000)=D2,D2=""),"",MIN(IF($A$2: $A$1000D2,$A$2:$A$1000))) In Col E use the previous formula itself If this post helps click Yes --------------- Jacob Skaria "Nigel P" wrote: "Jacob Skaria" wrote: Forgot to mention to selection the date column only DataFilterAdvanced FilterSelect Copy to another location..Select the date column... If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Put headers for your data. Use advanced filter to generate unique records to say Col D DataFilterAdvanced FilterSelect Copy to another location..Select the list range, Mention 'copy to' as (say cell D1). Check "Unique Records only'..OK In E2 use the below formula and copy that down =SUMIF(A:A,D2,B:B) If this post helps click Yes --------------- Jacob Skaria "Nigel P" wrote: I would like to summarise data by date for example A1 B1 - Date, Value 1/2/9 10 1/2/9 12 2/2/9 34 3/2/9 27 4/2/9 56 with results in another range of cells: 1/2/9 22 2/2/9 34 3/2/9 27 4/2/9 56 Hi thanks for the help. It gives the desired result. One small question though, is it possible to keep adding values in the colums A and B after performing the Advanced filter copy, and see the new data appear in the summary columns automatically? I want to give this spreadsheet to a friend to continue to add data too, and would not want to ask the person to do Advanced filter copy. thanks |
Summing Data by Date
Nigel P;392126 Wrote: I would like to summarise data by date for example A1 B1 - Date, Value 1/2/9 10 1/2/9 12 2/2/9 34 3/2/9 27 4/2/9 56 with results in another range of cells: 1/2/9 22 2/2/9 34 3/2/9 27 4/2/9 56 If your dates are truly sequential like that... then you can enter the first date somewhere and fill down the column to get the sequential dates...then in adjacent column, use SUMIF... e.g =Sumif($A$1:$A$100,X1,$B$1:$B$100) where A1:B100 contain your original table of dates/values and X1 contains first date to summarize... ... alternatively look into Pivot tables... (Google Pivot Tables Tutorials, and similar). -- NBVC Where there is a will there are many ways. ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=109598 |
| All times are GMT +1. The time now is 07:25 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com