ExcelBanter - Counting data by week number

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Counting data by week number (https://www.excelbanter.com/excel-worksheet-functions/106348-counting-data-week-number.html)

Counting data by week number

Hi,

I have a formula set up to count the number of e-mails answered by category
for each month. The formula being used is:

=SUMPRODUCT(--(MONTH(Pivot!$E$8:$E$1589)=MONTH($B$1)),--(Pivot!$E$8:$E$1589<"")*(Pivot!$J$8:$J$1589=A24))

Where column E contains the date the e-mail was answered; Cell B1 contains
the date I'm checking and column J contains the category assigned to the
e-mail and cell A24 is the category I'm trying to get the end result for.

I'd like to do the same thing but calculate it by week. I was feeling
rather confident and just substituted "WEEKNUM" for "MONTH" to have:

=SUMPRODUCT(--(WEEKNUM(Pivot!$E$8:$E$1589)=WEEKNUM($B$1)),--(Pivot!$E$8:$E$1589<"")*(Pivot!$J$8:$J$1589=A24))

But I get a result of: #VALUE!

Can somebody offer advice on where I've gone wrong?

Thanks in advance,
Scott

Counting data by week number

WEEKNUM won't work with arrays.

Biff

"Scopar" wrote in message
...
Hi,

I have a formula set up to count the number of e-mails answered by
category
for each month. The formula being used is:

=SUMPRODUCT(--(MONTH(Pivot!$E$8:$E$1589)=MONTH($B$1)),--(Pivot!$E$8:$E$1589<"")*(Pivot!$J$8:$J$1589=A24))

Where column E contains the date the e-mail was answered; Cell B1 contains
the date I'm checking and column J contains the category assigned to the
e-mail and cell A24 is the category I'm trying to get the end result for.

I'd like to do the same thing but calculate it by week. I was feeling
rather confident and just substituted "WEEKNUM" for "MONTH" to have:

=SUMPRODUCT(--(WEEKNUM(Pivot!$E$8:$E$1589)=WEEKNUM($B$1)),--(Pivot!$E$8:$E$1589<"")*(Pivot!$J$8:$J$1589=A24))

But I get a result of: #VALUE!

Can somebody offer advice on where I've gone wrong?

Thanks in advance,
Scott

Counting data by week number

Try this

=SUMPRODUCT(--(1+INT((Pivot!$E$8:$E$1589-(DATE(YEAR(Pivot!$E$8:$E$1589),1,2)
-WEEKDAY(DATE(YEAR(Pivot!$E$8:$E$1589),1,1))))/7)=WEEKNUM($B$1)),
--(Pivot!$E$8:$E$1589<""),--(Pivot!$J$8:$J$1589=A24))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Scopar" wrote in message
...
Hi,

I have a formula set up to count the number of e-mails answered by
category
for each month. The formula being used is:

=SUMPRODUCT(--(MONTH(Pivot!$E$8:$E$1589)=MONTH($B$1)),--(Pivot!$E$8:$E$1589<
"")*(Pivot!$J$8:$J$1589=A24))

Where column E contains the date the e-mail was answered; Cell B1 contains
the date I'm checking and column J contains the category assigned to the
e-mail and cell A24 is the category I'm trying to get the end result for.

I'd like to do the same thing but calculate it by week. I was feeling
rather confident and just substituted "WEEKNUM" for "MONTH" to have:

=SUMPRODUCT(--(WEEKNUM(Pivot!$E$8:$E$1589)=WEEKNUM($B$1)),--(Pivot!$E$8:$E$1
589<"")*(Pivot!$J$8:$J$1589=A24))

But I get a result of: #VALUE!

Can somebody offer advice on where I've gone wrong?

Thanks in advance,
Scott

Counting data by week number

Wow! That's brilliant! I'd like to ask why this formula works, but I think
it would be a little beyond me. Besides working it out will give me
something to do over the next few months. :o)

Thank you for your advice.

Scott

"Bob Phillips" wrote:

Try this

=SUMPRODUCT(--(1+INT((Pivot!$E$8:$E$1589-(DATE(YEAR(Pivot!$E$8:$E$1589),1,2)
-WEEKDAY(DATE(YEAR(Pivot!$E$8:$E$1589),1,1))))/7)=WEEKNUM($B$1)),
--(Pivot!$E$8:$E$1589<""),--(Pivot!$J$8:$J$1589=A24))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Scopar" wrote in message
...
Hi,

I have a formula set up to count the number of e-mails answered by
category
for each month. The formula being used is:

=SUMPRODUCT(--(MONTH(Pivot!$E$8:$E$1589)=MONTH($B$1)),--(Pivot!$E$8:$E$1589<
"")*(Pivot!$J$8:$J$1589=A24))

Where column E contains the date the e-mail was answered; Cell B1 contains
the date I'm checking and column J contains the category assigned to the
e-mail and cell A24 is the category I'm trying to get the end result for.

I'd like to do the same thing but calculate it by week. I was feeling
rather confident and just substituted "WEEKNUM" for "MONTH" to have:

=SUMPRODUCT(--(WEEKNUM(Pivot!$E$8:$E$1589)=WEEKNUM($B$1)),--(Pivot!$E$8:$E$1
589<"")*(Pivot!$J$8:$J$1589=A24))

But I get a result of: #VALUE!

Can somebody offer advice on where I've gone wrong?

Thanks in advance,
Scott

Counting data by week number

It's just a simple (<g) little weekday replacement formula because, as Biff
said, WEEKDAY will not return an array of values that SUMPRODUCT can work
on, so we need to build a formula that does return such an array.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Scopar" wrote in message
...
Wow! That's brilliant! I'd like to ask why this formula works, but I
think
it would be a little beyond me. Besides working it out will give me
something to do over the next few months. :o)

Thank you for your advice.

Scott

"Bob Phillips" wrote:

Try this

=SUMPRODUCT(--(1+INT((Pivot!$E$8:$E$1589-(DATE(YEAR(Pivot!$E$8:$E$1589),1,2)
-WEEKDAY(DATE(YEAR(Pivot!$E$8:$E$1589),1,1))))/7)=WEEKNUM($B$1)),
--(Pivot!$E$8:$E$1589<""),--(Pivot!$J$8:$J$1589=A24))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Scopar" wrote in message
...
Hi,

I have a formula set up to count the number of e-mails answered by
category
for each month. The formula being used is:

=SUMPRODUCT(--(MONTH(Pivot!$E$8:$E$1589)=MONTH($B$1)),--(Pivot!$E$8:$E$1589<
"")*(Pivot!$J$8:$J$1589=A24))

Where column E contains the date the e-mail was answered; Cell B1
contains
the date I'm checking and column J contains the category assigned to
the
e-mail and cell A24 is the category I'm trying to get the end result
for.

I'd like to do the same thing but calculate it by week. I was feeling
rather confident and just substituted "WEEKNUM" for "MONTH" to have:

=SUMPRODUCT(--(WEEKNUM(Pivot!$E$8:$E$1589)=WEEKNUM($B$1)),--(Pivot!$E$8:$E$1
589<"")*(Pivot!$J$8:$J$1589=A24))

But I get a result of: #VALUE!

Can somebody offer advice on where I've gone wrong?

Thanks in advance,
Scott

Counting data by week number

Sorry, meant WEEKNUM not WEEKDAY

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Scopar" wrote in message
...
Wow! That's brilliant! I'd like to ask why this formula works, but I
think
it would be a little beyond me. Besides working it out will give me
something to do over the next few months. :o)

Thank you for your advice.

Scott

"Bob Phillips" wrote:

Try this

=SUMPRODUCT(--(1+INT((Pivot!$E$8:$E$1589-(DATE(YEAR(Pivot!$E$8:$E$1589),1,2)
-WEEKDAY(DATE(YEAR(Pivot!$E$8:$E$1589),1,1))))/7)=WEEKNUM($B$1)),
--(Pivot!$E$8:$E$1589<""),--(Pivot!$J$8:$J$1589=A24))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Scopar" wrote in message
...
Hi,

I have a formula set up to count the number of e-mails answered by
category
for each month. The formula being used is:

=SUMPRODUCT(--(MONTH(Pivot!$E$8:$E$1589)=MONTH($B$1)),--(Pivot!$E$8:$E$1589<
"")*(Pivot!$J$8:$J$1589=A24))

Where column E contains the date the e-mail was answered; Cell B1
contains
the date I'm checking and column J contains the category assigned to
the
e-mail and cell A24 is the category I'm trying to get the end result
for.

I'd like to do the same thing but calculate it by week. I was feeling
rather confident and just substituted "WEEKNUM" for "MONTH" to have:

=SUMPRODUCT(--(WEEKNUM(Pivot!$E$8:$E$1589)=WEEKNUM($B$1)),--(Pivot!$E$8:$E$1
589<"")*(Pivot!$J$8:$J$1589=A24))

But I get a result of: #VALUE!

Can somebody offer advice on where I've gone wrong?

Thanks in advance,
Scott