Weekly Totals on sheet
 

I have a Worksheet that will contain a set of stock transactions. Sometimes
there will be multiple lines (transactions) per day, sometime none at all.

On a second worksheet I would like to total the gains/losses by week with
Sunday being the first of the week.

Any ideas?

Weekly Totals on sheet
 

I would be inclined to use a pivot table. Place your cursor in the middle of
the data set and select Data - Pivot Table. Follow the wizard that comes up
(or just hit finish as the defaults will probably be correct). Drag quotes to
the middle and place the dates in the left column. You will probably want to
group based on the dates every 7 days.
--
HTH...

Jim Thomlinson


"Jerry" wrote:

I have a Worksheet that will contain a set of stock transactions. Sometimes
there will be multiple lines (transactions) per day, sometime none at all.

On a second worksheet I would like to total the gains/losses by week with
Sunday being the first of the week.

Any ideas?

Weekly Totals on sheet
 

Thanks Jim ,
But I would like to stay away from pivots if at all possible.
I would like to take the idea perhaps commerically (Keep it simple for the
user) and would like to keep the summary on the second "tab".


"Jim Thomlinson" wrote:

I would be inclined to use a pivot table. Place your cursor in the middle of
the data set and select Data - Pivot Table. Follow the wizard that comes up
(or just hit finish as the defaults will probably be correct). Drag quotes to
the middle and place the dates in the left column. You will probably want to
group based on the dates every 7 days.
--
HTH...

Jim Thomlinson


"Jerry" wrote:

I have a Worksheet that will contain a set of stock transactions. Sometimes
there will be multiple lines (transactions) per day, sometime none at all.

On a second worksheet I would like to total the gains/losses by week with
Sunday being the first of the week.

Any ideas?

Weekly Totals on sheet
 

How about using sum product formulas? Anything you can create with a pivot
you can create with sumproduct...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
HTH...

Jim Thomlinson


"Jerry" wrote:

Thanks Jim ,
But I would like to stay away from pivots if at all possible.
I would like to take the idea perhaps commerically (Keep it simple for the
user) and would like to keep the summary on the second "tab".


"Jim Thomlinson" wrote:

I would be inclined to use a pivot table. Place your cursor in the middle of
the data set and select Data - Pivot Table. Follow the wizard that comes up
(or just hit finish as the defaults will probably be correct). Drag quotes to
the middle and place the dates in the left column. You will probably want to
group based on the dates every 7 days.
--
HTH...

Jim Thomlinson


"Jerry" wrote:

I have a Worksheet that will contain a set of stock transactions. Sometimes
there will be multiple lines (transactions) per day, sometime none at all.

On a second worksheet I would like to total the gains/losses by week with
Sunday being the first of the week.

Any ideas?

Weekly Totals on sheet
 

That should work like a champ.
Wow - great function.
I haven't used that one before, but I will now

"Jim Thomlinson" wrote:

How about using sum product formulas? Anything you can create with a pivot
you can create with sumproduct...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
HTH...

Jim Thomlinson


"Jerry" wrote:

Thanks Jim ,
But I would like to stay away from pivots if at all possible.
I would like to take the idea perhaps commerically (Keep it simple for the
user) and would like to keep the summary on the second "tab".


"Jim Thomlinson" wrote:

I would be inclined to use a pivot table. Place your cursor in the middle of
the data set and select Data - Pivot Table. Follow the wizard that comes up
(or just hit finish as the defaults will probably be correct). Drag quotes to
the middle and place the dates in the left column. You will probably want to
group based on the dates every 7 days.
--
HTH...

Jim Thomlinson


"Jerry" wrote:

I have a Worksheet that will contain a set of stock transactions. Sometimes
there will be multiple lines (transactions) per day, sometime none at all.

On a second worksheet I would like to total the gains/losses by week with
Sunday being the first of the week.

Any ideas?

Weekly Totals on sheet
 

Sumproduct would be the slow way to do it and the more formulas you used the
slower it would get - and not necessary in this case.

=Sumif(A:A,"=01/01/2006",B:B)-Sumif(A:A,"01/07/2006",B:B)

would be more efficient.

--
Regards,
Tom Ogilvy


"Jerry" wrote in message
...
That should work like a champ.
Wow - great function.
I haven't used that one before, but I will now

"Jim Thomlinson" wrote:

How about using sum product formulas? Anything you can create with a
pivot
you can create with sumproduct...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
HTH...

Jim Thomlinson


"Jerry" wrote:

Thanks Jim ,
But I would like to stay away from pivots if at all possible.
I would like to take the idea perhaps commerically (Keep it simple for
the
user) and would like to keep the summary on the second "tab".


"Jim Thomlinson" wrote:

I would be inclined to use a pivot table. Place your cursor in the
middle of
the data set and select Data - Pivot Table. Follow the wizard that
comes up
(or just hit finish as the defaults will probably be correct). Drag
quotes to
the middle and place the dates in the left column. You will probably
want to
group based on the dates every 7 days.
--
HTH...

Jim Thomlinson


"Jerry" wrote:

I have a Worksheet that will contain a set of stock transactions.
Sometimes
there will be multiple lines (transactions) per day, sometime none
at all.

On a second worksheet I would like to total the gains/losses by
week with
Sunday being the first of the week.

Any ideas?

Weekly Totals on sheet
 

Tom -
so even if I copy that down 52+ rows and change the date, you think it might
be faster?

"Tom Ogilvy" wrote:

Sumproduct would be the slow way to do it and the more formulas you used the
slower it would get - and not necessary in this case.

=Sumif(A:A,"=01/01/2006",B:B)-Sumif(A:A,"01/07/2006",B:B)

would be more efficient.

--
Regards,
Tom Ogilvy


"Jerry" wrote in message
...
That should work like a champ.
Wow - great function.
I haven't used that one before, but I will now

"Jim Thomlinson" wrote:

How about using sum product formulas? Anything you can create with a
pivot
you can create with sumproduct...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
HTH...

Jim Thomlinson


"Jerry" wrote:

Thanks Jim ,
But I would like to stay away from pivots if at all possible.
I would like to take the idea perhaps commerically (Keep it simple for
the
user) and would like to keep the summary on the second "tab".


"Jim Thomlinson" wrote:

I would be inclined to use a pivot table. Place your cursor in the
middle of
the data set and select Data - Pivot Table. Follow the wizard that
comes up
(or just hit finish as the defaults will probably be correct). Drag
quotes to
the middle and place the dates in the left column. You will probably
want to
group based on the dates every 7 days.
--
HTH...

Jim Thomlinson


"Jerry" wrote:

I have a Worksheet that will contain a set of stock transactions.
Sometimes
there will be multiple lines (transactions) per day, sometime none
at all.

On a second worksheet I would like to total the gains/losses by
week with
Sunday being the first of the week.

Any ideas?

Weekly Totals on sheet
 

It would be significantly faster than doing the same thing with sumproduct.

If you are queasy about use A:A, then don't use it. Use A1:A100 and
B1:B100 if you wish - same as you would with sumproduct (of course these are
example ranges, but it is always amazing how some people take things
literally).

--
Regards,
Tom Ogilvy


"Jerry" wrote in message
...
Tom -
so even if I copy that down 52+ rows and change the date, you think it
might
be faster?

"Tom Ogilvy" wrote:

Sumproduct would be the slow way to do it and the more formulas you used
the
slower it would get - and not necessary in this case.

=Sumif(A:A,"=01/01/2006",B:B)-Sumif(A:A,"01/07/2006",B:B)

would be more efficient.

--
Regards,
Tom Ogilvy


"Jerry" wrote in message
...
That should work like a champ.
Wow - great function.
I haven't used that one before, but I will now

"Jim Thomlinson" wrote:

How about using sum product formulas? Anything you can create with a
pivot
you can create with sumproduct...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
HTH...

Jim Thomlinson


"Jerry" wrote:

Thanks Jim ,
But I would like to stay away from pivots if at all possible.
I would like to take the idea perhaps commerically (Keep it simple
for
the
user) and would like to keep the summary on the second "tab".


"Jim Thomlinson" wrote:

I would be inclined to use a pivot table. Place your cursor in the
middle of
the data set and select Data - Pivot Table. Follow the wizard
that
comes up
(or just hit finish as the defaults will probably be correct).
Drag
quotes to
the middle and place the dates in the left column. You will
probably
want to
group based on the dates every 7 days.
--
HTH...

Jim Thomlinson


"Jerry" wrote:

I have a Worksheet that will contain a set of stock
transactions.
Sometimes
there will be multiple lines (transactions) per day, sometime
none
at all.

On a second worksheet I would like to total the gains/losses by
week with
Sunday being the first of the week.

Any ideas?

Weekly Totals on sheet
 

literally
Yep - I would have been in that camp for a while at least ;-)

Thanks Tom

"Tom Ogilvy" wrote:

It would be significantly faster than doing the same thing with sumproduct.

If you are queasy about use A:A, then don't use it. Use A1:A100 and
B1:B100 if you wish - same as you would with sumproduct (of course these are
example ranges, but it is always amazing how some people take things
literally).

--
Regards,
Tom Ogilvy


"Jerry" wrote in message
...
Tom -
so even if I copy that down 52+ rows and change the date, you think it
might
be faster?

"Tom Ogilvy" wrote:

Sumproduct would be the slow way to do it and the more formulas you used
the
slower it would get - and not necessary in this case.

=Sumif(A:A,"=01/01/2006",B:B)-Sumif(A:A,"01/07/2006",B:B)

would be more efficient.

--
Regards,
Tom Ogilvy


"Jerry" wrote in message
...
That should work like a champ.
Wow - great function.
I haven't used that one before, but I will now

"Jim Thomlinson" wrote:

How about using sum product formulas? Anything you can create with a
pivot
you can create with sumproduct...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
HTH...

Jim Thomlinson


"Jerry" wrote:

Thanks Jim ,
But I would like to stay away from pivots if at all possible.
I would like to take the idea perhaps commerically (Keep it simple
for
the
user) and would like to keep the summary on the second "tab".


"Jim Thomlinson" wrote:

I would be inclined to use a pivot table. Place your cursor in the
middle of
the data set and select Data - Pivot Table. Follow the wizard
that
comes up
(or just hit finish as the defaults will probably be correct).
Drag
quotes to
the middle and place the dates in the left column. You will
probably
want to
group based on the dates every 7 days.
--
HTH...

Jim Thomlinson


"Jerry" wrote:

I have a Worksheet that will contain a set of stock
transactions.
Sometimes
there will be multiple lines (transactions) per day, sometime
none
at all.

On a second worksheet I would like to total the gains/losses by
week with
Sunday being the first of the week.

Any ideas?