| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT/COUNTIF
I am currently using the following formula to count the number of times an
item is in column "Complete_Date_May" is blank, when there is a date in "Post_Date_May" greater than 27 days old. =SUMPRODUCT(--(Post_Date_May<""),--(Post_Date_May<TODAY()-27),--(Complete_Date_May="")) This gives a total figure of outstanding post. I now wish to add that the corresponding name in column F must equal "Becky". I have tried the following: =SUMPRODUCT(--(Post_Date_May<""),--(Post_Date_May<TODAY()-27),--(Complete_Date_May=""),--($F2:$F9999=$J2)) Where J2 currently shows the name Becky. This however is returning #VALUE. How can I add this extra variable to my original formula? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT/COUNTIF
Are all the column sizes the same i.e 2 to 9999?
"luvthavodka" wrote: I am currently using the following formula to count the number of times an item is in column "Complete_Date_May" is blank, when there is a date in "Post_Date_May" greater than 27 days old. =SUMPRODUCT(--(Post_Date_May<""),--(Post_Date_May<TODAY()-27),--(Complete_Date_May="")) This gives a total figure of outstanding post. I now wish to add that the corresponding name in column F must equal "Becky". I have tried the following: =SUMPRODUCT(--(Post_Date_May<""),--(Post_Date_May<TODAY()-27),--(Complete_Date_May=""),--($F2:$F9999=$J2)) Where J2 currently shows the name Becky. This however is returning #VALUE. How can I add this extra variable to my original formula? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT/COUNTIF
"Post_Date_May" is defined as ='May 06'!$A$2:INDEX('May
06'!$A$2:$A$65536,MATCH(9.99999999999999E+307,'May 06'!$A$2:$A$65536)) "Complete_Date_May" is defined as='May 06'!$G$2:INDEX('May 06'!$G$2:$G$65536,MATCH(9.99999999999999E+307,'May 06'!$A$2:$A$65536)) I have just tried: =SUMPRODUCT(--(Post_Date_May<""),--(Post_Date_May<TODAY()-27),--(Complete_Date_May=""),--($F2:$F65536=$J2)) However this is also returning #VALUE???????!!!!! "Toppers" wrote: Are all the column sizes the same i.e 2 to 9999? "luvthavodka" wrote: I am currently using the following formula to count the number of times an item is in column "Complete_Date_May" is blank, when there is a date in "Post_Date_May" greater than 27 days old. =SUMPRODUCT(--(Post_Date_May<""),--(Post_Date_May<TODAY()-27),--(Complete_Date_May="")) This gives a total figure of outstanding post. I now wish to add that the corresponding name in column F must equal "Becky". I have tried the following: =SUMPRODUCT(--(Post_Date_May<""),--(Post_Date_May<TODAY()-27),--(Complete_Date_May=""),--($F2:$F9999=$J2)) Where J2 currently shows the name Becky. This however is returning #VALUE. How can I add this extra variable to my original formula? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT/COUNTIF
You need to ensure Column F is the same size as the others whose length is
defined by the INDEX function. "luvthavodka" wrote: "Post_Date_May" is defined as ='May 06'!$A$2:INDEX('May 06'!$A$2:$A$65536,MATCH(9.99999999999999E+307,'May 06'!$A$2:$A$65536)) "Complete_Date_May" is defined as='May 06'!$G$2:INDEX('May 06'!$G$2:$G$65536,MATCH(9.99999999999999E+307,'May 06'!$A$2:$A$65536)) I have just tried: =SUMPRODUCT(--(Post_Date_May<""),--(Post_Date_May<TODAY()-27),--(Complete_Date_May=""),--($F2:$F65536=$J2)) However this is also returning #VALUE???????!!!!! "Toppers" wrote: Are all the column sizes the same i.e 2 to 9999? "luvthavodka" wrote: I am currently using the following formula to count the number of times an item is in column "Complete_Date_May" is blank, when there is a date in "Post_Date_May" greater than 27 days old. =SUMPRODUCT(--(Post_Date_May<""),--(Post_Date_May<TODAY()-27),--(Complete_Date_May="")) This gives a total figure of outstanding post. I now wish to add that the corresponding name in column F must equal "Becky". I have tried the following: =SUMPRODUCT(--(Post_Date_May<""),--(Post_Date_May<TODAY()-27),--(Complete_Date_May=""),--($F2:$F9999=$J2)) Where J2 currently shows the name Becky. This however is returning #VALUE. How can I add this extra variable to my original formula? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT/COUNTIF
I've named column F "Underwriter_May", defined as ='May 06'!$F$2:INDEX('May
06'!$F$2:$F$65536,MATCH(9.99999999999999E+307,'May 06'!$F$2:$F$65536)) However this isn't working? "Toppers" wrote: You need to ensure Column F is the same size as the others whose length is defined by the INDEX function. "luvthavodka" wrote: "Post_Date_May" is defined as ='May 06'!$A$2:INDEX('May 06'!$A$2:$A$65536,MATCH(9.99999999999999E+307,'May 06'!$A$2:$A$65536)) "Complete_Date_May" is defined as='May 06'!$G$2:INDEX('May 06'!$G$2:$G$65536,MATCH(9.99999999999999E+307,'May 06'!$A$2:$A$65536)) I have just tried: =SUMPRODUCT(--(Post_Date_May<""),--(Post_Date_May<TODAY()-27),--(Complete_Date_May=""),--($F2:$F65536=$J2)) However this is also returning #VALUE???????!!!!! "Toppers" wrote: Are all the column sizes the same i.e 2 to 9999? "luvthavodka" wrote: I am currently using the following formula to count the number of times an item is in column "Complete_Date_May" is blank, when there is a date in "Post_Date_May" greater than 27 days old. =SUMPRODUCT(--(Post_Date_May<""),--(Post_Date_May<TODAY()-27),--(Complete_Date_May="")) This gives a total figure of outstanding post. I now wish to add that the corresponding name in column F must equal "Becky". I have tried the following: =SUMPRODUCT(--(Post_Date_May<""),--(Post_Date_May<TODAY()-27),--(Complete_Date_May=""),--($F2:$F9999=$J2)) Where J2 currently shows the name Becky. This however is returning #VALUE. How can I add this extra variable to my original formula? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT/COUNTIF
What happens with SUMPRODUCT just on colum F
=SUMPRODUCT(--(Underwriter_May="Becky") OR =SUMPRODUCT(--(Underwriter_May$J2) "luvthavodka" wrote: I've named column F "Underwriter_May", defined as ='May 06'!$F$2:INDEX('May 06'!$F$2:$F$65536,MATCH(9.99999999999999E+307,'May 06'!$F$2:$F$65536)) However this isn't working? "Toppers" wrote: You need to ensure Column F is the same size as the others whose length is defined by the INDEX function. "luvthavodka" wrote: "Post_Date_May" is defined as ='May 06'!$A$2:INDEX('May 06'!$A$2:$A$65536,MATCH(9.99999999999999E+307,'May 06'!$A$2:$A$65536)) "Complete_Date_May" is defined as='May 06'!$G$2:INDEX('May 06'!$G$2:$G$65536,MATCH(9.99999999999999E+307,'May 06'!$A$2:$A$65536)) I have just tried: =SUMPRODUCT(--(Post_Date_May<""),--(Post_Date_May<TODAY()-27),--(Complete_Date_May=""),--($F2:$F65536=$J2)) However this is also returning #VALUE???????!!!!! "Toppers" wrote: Are all the column sizes the same i.e 2 to 9999? "luvthavodka" wrote: I am currently using the following formula to count the number of times an item is in column "Complete_Date_May" is blank, when there is a date in "Post_Date_May" greater than 27 days old. =SUMPRODUCT(--(Post_Date_May<""),--(Post_Date_May<TODAY()-27),--(Complete_Date_May="")) This gives a total figure of outstanding post. I now wish to add that the corresponding name in column F must equal "Becky". I have tried the following: =SUMPRODUCT(--(Post_Date_May<""),--(Post_Date_May<TODAY()-27),--(Complete_Date_May=""),--($F2:$F9999=$J2)) Where J2 currently shows the name Becky. This however is returning #VALUE. How can I add this extra variable to my original formula? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sumproduct/countif function help | Excel Worksheet Functions | |||
| SumProduct/CountIf dilemna + Date Range | Excel Worksheet Functions | |||
Hybrid Mode
