Formula Question

[JimS]

I made a new copy of my spreadsheet eliminating all of the blank rows
and adding a date for every entry


1/4/09 -23
1/4/09 88
1/4/09 -14
1/5/09 77
1/6/09 88
1/6/09 -44
1/7/09 55
1/7/09 13

In my old sheet I could do day (and hour) totals. I don't know how do
to that with the new format.

How can I do daily totals, something like the following:

1/4/09 -23
1/4/09 88
1/4/09 -14 -29
1/5/09 77 77
1/6/09 88
1/6/09 -44 44
1/7/09 55
1/7/09 13 68

The numbers off to the right are day totals. Can I do this?

 

[JimS]

I should say I've been studying the posts on pivot tables and learning
how to use them. I made a pivot table of the same data underneath and
it worked out fine, except that six of the data points are out of
order. There are six dates at the end of the list that don't belong
there.

It looks like following, and I don't understand what I'm doing wrong.

1/21/09
1/22/09
1/23/09
1/24/09
4/6/09
6/14/09
6/20/09
8/3/09
8/12/09
8/21/09
11/29/09
12/8/09

After 1/24/09 it goes to 4/6, and then 6/14 etc. They are in the
proper order in the spread sheet. This happend when I made a pivot
table.

 

[muddan madhu]

try this !

=IF(A2<>A3,SUM($B$2:$B2)-SUM(C$1:$C1),"")

in Pivot After 24-jan obviously it will be 6-Apr

 

[Pete_UK]

If the dates are sorted, you can do this in C1:

=IF(A1<>A2,SUMIF(A:A,A1,B:B),"")

and copy down as required to get your daily totals against the last
entry for that day.

Hope this helps.

Pete

 

[JimS]

I couldn't get this to work: I made a mistake on the table. Should
look like this:

1/4/2009 -23
1/4/2009 88
1/4/2009 -14 51
1/5/2009 77 77
1/6/2009 88
1/6/2009 -44 44
1/7/2009 55
1/7/2009 13 68

The 51, 77, 44 and 68 are the answers I'm trying to come up with in
the C column. Your formula is using the C column and there is nothing
in there yet, so I'm not quite following.

 

[JimS]

Actually, I got the other formula to work as well. I was putting it
in the wrong cell. Thanks to you both.

Jim

 

[Pete_UK]

You're welcome, Jim - thanks for feeding back.

Pete

 

[Dave Peterson]

Did you sort the pivottable.

Rightclick on the field label
Choose Field Settings
Click on the advanced button
and make sure that the sort is what you you want.

If that doesn't help, make sure that the data is really a date in the original
table.

=isnumber()
is one way to check