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

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

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

"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

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

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

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