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

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" wrote in message
...
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

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.




"Bob Phillips" wrote in message
...
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" wrote in message
...
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

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" wrote:

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.




"Bob Phillips" wrote in message
...
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" wrote in message
...
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

Great thanks, the helper column will work great.

"daddylonglegs" wrote in message
...
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" wrote:

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.




"Bob Phillips" wrote in message
...
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" wrote in message
...
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