Data counting

[Nobody I via OfficeKB.com]

hi there,

I need a solution for excel.

Sheet1. This worksheet excists of a column DATE where a user can fill in
dates. These dates are of the format: dd/mm/yyyy .
And a column AMOUNT. Where a user can fill in an amount.
This amount is of the format: number, decimal 2

for example:

05/07/2005 | 15,30
12/07/2005 | 34,80
12/07/2005 | 8,50
12/07/2005 | 12,90
20/07/2005 | 5,00
20/07/2005 | 7,50
24/07/2005 | 100,60

What I want to accomplish is the following.

Excel has to make a SOM from the amounts per date. and list these SOMs on
sheet2.

in the format:

05/07/2005 | 15,30
12/07/2005 | 56,20
20/07/2005 | 12,50
24/07/2005 | 100,60


Can excel do this?
Edit/Delete Message

 

[David McRitchie]

Since your dates appear to be in order, I would suggest
that you use Subtotals in the Data menu.

Insert a title row above your data i.e. Date Amount

Select all cells Ctrl+A
(in Excel 2003 you had best use Ctrl+Shift+Spacebar)

Data, Subtotals
At each change in: Date
Use function: Sum
add subtotal to: Amount

Click on button [2] to see the total for each date

If you want to get fancy you can change coloration by
first reducing selection to the visible cells and then formatting
as desired.
Ctrl+(semi-colon) (button has 4 black rectangles inside)
use the Fill Color button (button is a paint can)

To eliminate all use of Subtotals
data, subtotals, remove all

 

[Debra Dalgleish]

You can use a pivot table to summarize the data by date. There are
instructions in Excel's Help, and Jon Peltier has information
and links:

http://peltiertech.com/Excel/Pivots/pivotstart.htm


Add Date to the row area, and Amount to the data area, where
it will become Sum of Amount.

 

[Jim May]

With your given data in A1:B7, click in Cell D1
then at the menu Data, Filter, Advanced-Filter,
List range s/b = $A$1:$A$7; Criteria range - leave blank;
click Copy to another range, then in Copy to: enter D1,
finally click on the Unique records Only box.; OK
D14 << should be your unique dates;
In Cell E1 enter: =Sumproduct(($A$1:$A$7=D1)-($B$1:$B$7))
Copy E1 down to E4
HTH;

 

[Jim May]

Yikes!!
=Sumproduct(($A$1:$A$7=D1)-($B$1:$B$7))
should be:
=Sumproduct(($A$1:$A$7=D1)*($B$1:$B$7))