| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Week Numbers (Counting Backwards) | Excel Discussion (Misc queries) | |||
| Vlookup to Return a Range of Data | Excel Discussion (Misc queries) | |||
| Macro to insert copied cells | Excel Discussion (Misc queries) | |||
| evaluate data by week number | Excel Worksheet Functions | |||
| Convert data of cells to any type: Number, Date&Time, Text | Excel Discussion (Misc queries) | |||
Linear Mode
