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 "week of Feb-19"

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.




"Jspearm" wrote in message
...
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 "week of Feb-19"

Try this formula, I had it backwards
=SUMIF(C$1:C$20,WEEKNUM(E1),B$1:B$20)

"Jspearm" wrote in message
...
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 "week of Feb-19"

Barb Reinhardt Wrote:
Try this formula, I had it backwards
=SUMIF(C$1:C$20,WEEKNUM(E1),B$1:B$20)

"Jspearm" wrote in message
...
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 "week of Feb-19"


I thought tool packs wont work with conjuction with other formula. Am I
wrong*?*


--
vane0326
------------------------------------------------------------------------
vane0326's Profile: http://www.excelforum.com/member.php...o&userid=14731
View this thread: http://www.excelforum.com/showthread...hreadid=514208

Yes you are wrong

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"vane0326" wrote in
message ...

Barb Reinhardt Wrote:
Try this formula, I had it backwards
=SUMIF(C$1:C$20,WEEKNUM(E1),B$1:B$20)

"Jspearm" wrote in message
...
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 "week of Feb-19"


I thought tool packs wont work with conjuction with other formula. Am I
wrong*?*


--
vane0326
------------------------------------------------------------------------
vane0326's Profile:
http://www.excelforum.com/member.php...o&userid=14731
View this thread: http://www.excelforum.com/showthread...hreadid=514208

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: http://www.excelforum.com/member.php...o&userid=14731
View this thread: http://www.excelforum.com/showthread...hreadid=514208

It works for me.

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

vane0326 wrote:

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: http://www.excelforum.com/member.php...o&userid=14731
View this thread: http://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: http://www.excelforum.com/member.php...o&userid=14731
View this thread: http://www.excelforum.com/showthread...hreadid=514208

Weeknumbers 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)=($E1+1-WEEKDAY($E$1))),
--(($C$1:$C$20)<($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
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html


vane0326 wrote:

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: http://www.excelforum.com/member.php...o&userid=14731
View this thread: http://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: http://www.excelforum.com/member.php...o&userid=14731
View this thread: http://www.excelforum.com/showthread...hreadid=514208