How do I sum multiple days worth of info, into weekly entries.

[Guest]

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]

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.

 

[Barb Reinhardt]

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

 

[vane0326]

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

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

 

[Peo Sjoblom]

Yes you are wrong

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

 

[vane0326]

So you can use WEEKNUM in an arrays*?* If so I could'nt get this formul
to work.


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

 

[Dave Peterson]

It works for me.

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

 

[vane0326]

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

sorry about that

 

[Dave Peterson]

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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

 

[vane0326]

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

Once again Thank You