software

I am tracking my poker winnings and have a list of tournaments and their
dates, payouts, stuff like that. On one worksheet I have information on each
tournament. On the next worksheet, I want to include all the tournaments
from sunday until Sunday and have information on weekly performance. Ex,
under a title of quot;week of Feb-19quot;

You're going to have to do this in stages.

I think what you want to do is group them by WEEKNUM.
I don't know how you want to group them. Do you want to sum the winnings.
Let's say your dates are in column A1:A20 and your winnings are in column
B1:B20
Create a helper column in C1:C20 with the following formula
C1: =WEEKNUM(A1)
Copy down to C20

I'm doing the rest on the same sheet.

In column E, I have the Sunday Dates. THe first date is in E1.
In F1, put this formula

=SUMIF(B$1:B$20,WEEKNUM(E1),C$1:C$20)

I just realized that the SUMIF formula only gives a positive value. Maybe
someone else can help to fix this.

quot;Jspearmquot; gt; wrote in message
...
gt;I am tracking my poker winnings and have a list of tournaments and their
gt; dates, payouts, stuff like that. On one worksheet I have information on
gt; each
gt; tournament. On the next worksheet, I want to include all the tournaments
gt; from sunday until Sunday and have information on weekly performance. Ex,
gt; under a title of quot;week of Feb-19quot;
Try this formula, I had it backwards
=SUMIF(C$1:C$20,WEEKNUM(E1),B$1:B$20)

quot;Jspearmquot; gt; wrote in message
...
gt;I am tracking my poker winnings and have a list of tournaments and their
gt; dates, payouts, stuff like that. On one worksheet I have information on
gt; each
gt; tournament. On the next worksheet, I want to include all the tournaments
gt; from sunday until Sunday and have information on weekly performance. Ex,
gt; under a title of quot;week of Feb-19quot;

Barb Reinhardt Wrote:
gt; Try this formula, I had it backwards
gt; =SUMIF(C$1:C$20,WEEKNUM(E1),B$1:B$20)
gt;
gt; quot;Jspearmquot; gt; wrote in message
gt; ...
gt; gt;I am tracking my poker winnings and have a list of tournaments and
gt; their
gt; gt; dates, payouts, stuff like that. On one worksheet I have information
gt; on
gt; gt; each
gt; gt; tournament. On the next worksheet, I want to include all the
gt; tournaments
gt; gt; from sunday until Sunday and have information on weekly performance.
gt; Ex,
gt; gt; under a title of quot;week of Feb-19quot;I thought tool packs wont work with conjuction with other formula. Am I
wrong*?*--
vane0326
------------------------------------------------------------------------
vane0326's Profile: www.excelforum.com/member.php...oamp;userid=14731
View this thread: www.excelforum.com/showthread...hreadid=514208Yes you are wrong

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

quot;vane0326quot; gt; wrote in
message ...
gt;
gt; Barb Reinhardt Wrote:
gt;gt; Try this formula, I had it backwards
gt;gt; =SUMIF(C$1:C$20,WEEKNUM(E1),B$1:B$20)
gt;gt;
gt;gt; quot;Jspearmquot; gt; wrote in message
gt;gt; ...
gt;gt; gt;I am tracking my poker winnings and have a list of tournaments and
gt;gt; their
gt;gt; gt; dates, payouts, stuff like that. On one worksheet I have information
gt;gt; on
gt;gt; gt; each
gt;gt; gt; tournament. On the next worksheet, I want to include all the
gt;gt; tournaments
gt;gt; gt; from sunday until Sunday and have information on weekly performance.
gt;gt; Ex,
gt;gt; gt; under a title of quot;week of Feb-19quot;
gt;
gt;
gt; I thought tool packs wont work with conjuction with other formula. Am I
gt; wrong*?*
gt;
gt;
gt; --
gt; vane0326
gt; ------------------------------------------------------------------------
gt; vane0326's Profile:
gt; www.excelforum.com/member.php...oamp;userid=14731
gt; View this thread: www.excelforum.com/showthread...hreadid=514208
gt;
So you can use WEEKNUM in an arrays*?* If so I could'nt get this formula
to work.=SUMIF(C$1:C$20,WEEKNUM(E1),B$1:B$20)--
vane0326
------------------------------------------------------------------------
vane0326's Profile: www.excelforum.com/member.php...oamp;userid=14731
View this thread: www.excelforum.com/showthread...hreadid=514208It works for me.

Make sure you have a date in E1 and week numbers in c1:c20.

vane0326 wrote:
gt;
gt; So you can use WEEKNUM in an arrays*?* If so I could'nt get this formula
gt; to work.
gt;
gt; =SUMIF(C$1:C$20,WEEKNUM(E1),B$1:B$20)
gt;
gt; --
gt; vane0326
gt; ------------------------------------------------------------------------
gt; vane0326's Profile: www.excelforum.com/member.php...oamp;userid=14731
gt; View this thread: www.excelforum.com/showthread...hreadid=514208

--

Dave Peterson


Ok I got confuse. So I need a helper column using WEEKNUM formula. I
thought this is a shorter way to sum up by weeks. Meaning if I have a
list of dates in column C of 2 years and cell E1 is the cell that
represent of the week number and column B contains the vaules. All i
have to do is to change the week number in cell E1 say week *2* then
the formula will sum up all the dates for the past 2 years that falls
on week 2. I do have a long formula for that but dummie me
But I thought that was the shorter version.

sorry about that.--
vane0326
------------------------------------------------------------------------
vane0326's Profile: www.excelforum.com/member.php...oamp;userid=14731
View this thread: www.excelforum.com/showthread...hreadid=514208Weeknumbers start over every year. So checking the date to see if it's in week
number 2 may not do what you want--unless you really wanted the sum of both
years.

I think I'd use data|pivottable and group those dates by days and then choose 7
for the number of days (in the group dialog).

Or alternatively, you could keep the dates in E1, and C1:C20 and use a formula
like this:

=SUMPRODUCT(--(($C$1:$C$20)gt;=($E1 1-WEEKDAY($E$1))),
--(($C$1:$C$20)lt;($E$1 7 1-WEEKDAY($E$1))),
($B$1:$B$20))

(all one cell)

This portion of the formula:
$E1 1-WEEKDAY($E$1)
will return the the date of the previous Sunday (or the date is a Sunday, that
date)

For example, all these dates:
03/19/2006 Sunday
03/20/2006 Monday
03/21/2006 Tuesday
03/22/2006 Wednesday
03/23/2006 Thursday
03/24/2006 Friday
03/25/2006 Saturday

will return 03/19/2006.

This portion will return the next Sunday (7 days more)
$E$1 7 1-WEEKDAY($E$1)

So by typing any date in E1, you're checking to see if the date in C1:C20 is
between those two Sundays.

If it is, it adds the value in B1:B20.

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
mcgimpsey.com/excel/formulae/doubleneg.htmlvane0326 wrote:
gt;
gt; Ok I got confuse. So I need a helper column using WEEKNUM formula. I
gt; thought this is a shorter way to sum up by weeks. Meaning if I have a
gt; list of dates in column C of 2 years and cell E1 is the cell that
gt; represent of the week number and column B contains the vaules. All i
gt; have to do is to change the week number in cell E1 say week *2* then
gt; the formula will sum up all the dates for the past 2 years that falls
gt; on week 2. I do have a long formula for that but dummie me
gt; But I thought that was the shorter version.
gt;
gt; sorry about that.
gt;
gt; --
gt; vane0326
gt; ------------------------------------------------------------------------
gt; vane0326's Profile: www.excelforum.com/member.php...oamp;userid=14731
gt; View this thread: www.excelforum.com/showthread...hreadid=514208

--

Dave Peterson


Thanks Dave I will see if I could adjust what I'm doing.

Once again Thank You!--
vane0326
------------------------------------------------------------------------
vane0326's Profile: www.excelforum.com/member.php...oamp;userid=14731
View this thread: www.excelforum.com/showthread...hreadid=514208