Summing Data by Date

  • Thread starter Thread starter Nigel P
  • Start date Start date
N

Nigel P

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

Data>Filter>Advanced Filter>Select 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
 
Forgot to mention to selection the date column only

Data>Filter>Advanced Filter>Select Copy to another location..Select the date
column...

If this post helps click Yes
 
Jacob Skaria said:
Forgot to mention to selection the date column only

Data>Filter>Advanced Filter>Select Copy to another location..Select the date
column...

If this post helps click Yes
Click to expand...

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$1000>D2,$A$2:$A$1000)))

In Col E use the previous formula itself

If this post helps click Yes
 
Back
Top