WEEKNUM and SUMPRODUCT

[Doug VanDerMark]

Can they be used together? Excel 2003 is my version. I want to count the
number of transactions per week for each of my offices.

Basically, I am not trying to make this fancy at all.
Offices are listed in rows and the weeks are in the columns.

02/11/2007 02/18/2007
02/17/2007 02/24/2007
------------------------------------------------------------------------
New York 5 23
------------------------------------------------------------------------
St. Louis 17 14
------------------------------------------------------------------------

02/11/2007 is week 7
02/18/2007 is week 8

Something like if Office = "New York" and Weeknum = 7, etc.

Thanks in advance

 

[Bob Phillips]

You know you can't <g>

Try

=SUMPRODUCT((1+INT(($B1:$M1-(DATE(YEAR($B1:$M1),1,2)
-WEEKDAY(DATE(YEAR(TODAY()),1,1))))/7)=7)*(A3:A10="New York"),B3:M10)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Doug VanDerMark]

Hi Bob,

Can you explain how this works, I see where we get the number 7 of the week.
However, I don't need to calculate the date, i think that is where I am
confused. I am going to manually input the dates.

Here is my sample data

A B
2/13/2007 New York
2/13/2007 New York
2/13/2007 New York
2/13/2007 New York
2/13/2007 New York
2/13/2007 St. Louis
2/13/2007 St. Louis


I modified your formula and it comes up with 0

=SUMPRODUCT((1+INT((Sheet1!$A1:$B1-(DATE(YEAR(Sheet1!$A1:$B1),1,2)
-WEEKDAY(DATE(YEAR(TODAY()),1,1))))/7)=7)*(Sheet1!A2:A10="New
York"),Sheet1!A2:B10)

Results expected

7 8
New York 5
St. Louis 2

Basically, I can put anything I want in the "7" and "8" value even though I
will put in the dates of the week, but it is irrelevant for what I want to
do. Just need the count of entries for that week = 7.

 

[daddylonglegs]

Personally I'd use another column to calculate the week number, e.g. in C2
=WEEKNUM(A2) copied down column then you can use a formula like


=SUMPRODUCT(--(C2:C10=7),--(B2:B10="New York"))

but if you don't want another column then, as WEEKNUM doesn't accept an
array argument, you need to use another way within SUMPRODUCT to calculate
the week number, I think this is a bit shorter than Bob's method.....


=SUMPRODUCT(--(2+INT((A2:A10-DATE(YEAR(A2:A10),1,1)-WEEKDAY(A2:A10))/7)=7),--(B2:B10="New York"))

 

[Doug VanDerMark]

Great thanks, the helper column will work great.

Personally I'd use another column to calculate the week number, e.g. in C2
=WEEKNUM(A2) copied down column then you can use a formula like


=SUMPRODUCT(--(C2:C10=7),--(B2:B10="New York"))

but if you don't want another column then, as WEEKNUM doesn't accept an
array argument, you need to use another way within SUMPRODUCT to calculate
the week number, I think this is a bit shorter than Bob's method.....


=SUMPRODUCT(--(2+INT((A2:A10-DATE(YEAR(A2:A10),1,1)-WEEKDAY(A2:A10))/7)=7),--(B2:B10="New
York"))