| 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? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT/COUNTIF
Hmmmm... still returns #N/A...this has had me stumped for days!!!!
"Toppers" wrote: 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? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT/COUNTIF
Do you want to post me your file (toppers<atjohntopley.fsnet.co.uk). It
might wait until tomorrow as it's getting latie here in the UK! "luvthavodka" wrote: Hmmmm... still returns #N/A...this has had me stumped for days!!!! "Toppers" wrote: 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? |
|
#9
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,'Ma y 06'!$F$2:$F$65536)) That formula only works if the values are numeric. Since you're looking for "Becky" with is TEXT change this portion: MATCH(9.99999999999999E+307 Change to: MATCH(REPT("z",255) Note that this assumes column F ONLY contains TEXT values! Biff "Toppers" wrote in message ... Do you want to post me your file (toppers<atjohntopley.fsnet.co.uk). It might wait until tomorrow as it's getting latie here in the UK! "luvthavodka" wrote: Hmmmm... still returns #N/A...this has had me stumped for days!!!! "Toppers" wrote: 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? |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT/COUNTIF
P.S.
Also note, just because you're using a dynamic range each range MUST still end on the same row. Biff "Biff" wrote in message ... I've named column F "Underwriter_May", defined as ='May 06'!$F$2:INDEX('May 06'!$F$2:$F$65536,MATCH(9.99999999999999E+307,'M ay 06'!$F$2:$F$65536)) That formula only works if the values are numeric. Since you're looking for "Becky" with is TEXT change this portion: MATCH(9.99999999999999E+307 Change to: MATCH(REPT("z",255) Note that this assumes column F ONLY contains TEXT values! Biff "Toppers" wrote in message ... Do you want to post me your file (toppers<atjohntopley.fsnet.co.uk). It might wait until tomorrow as it's getting latie here in the UK! "luvthavodka" wrote: Hmmmm... still returns #N/A...this has had me stumped for days!!!! "Toppers" wrote: 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? |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT/COUNTIF
Thanks Biff, thats great. Just for future reference, why 255?
"Biff" 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,'Ma y 06'!$F$2:$F$65536)) That formula only works if the values are numeric. Since you're looking for "Becky" with is TEXT change this portion: MATCH(9.99999999999999E+307 Change to: MATCH(REPT("z",255) Note that this assumes column F ONLY contains TEXT values! Biff "Toppers" wrote in message ... Do you want to post me your file (toppers<atjohntopley.fsnet.co.uk). It might wait until tomorrow as it's getting latie here in the UK! "luvthavodka" wrote: Hmmmm... still returns #N/A...this has had me stumped for days!!!! "Toppers" wrote: 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? |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT/COUNTIF
Think I've answered my oen question....255 just represents the max number of
characters in that cell? Hence any number of a high value could be used here? Thanks ever so much Biff!!!! "luvthavodka" wrote: Thanks Biff, thats great. Just for future reference, why 255? "Biff" 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,'Ma y 06'!$F$2:$F$65536)) That formula only works if the values are numeric. Since you're looking for "Becky" with is TEXT change this portion: MATCH(9.99999999999999E+307 Change to: MATCH(REPT("z",255) Note that this assumes column F ONLY contains TEXT values! Biff "Toppers" wrote in message ... Do you want to post me your file (toppers<atjohntopley.fsnet.co.uk). It might wait until tomorrow as it's getting latie here in the UK! "luvthavodka" wrote: Hmmmm... still returns #N/A...this has had me stumped for days!!!! "Toppers" wrote: 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? |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT/COUNTIF
Think I've answered my oen question....255 just represents the max number
of characters in that cell? Hence any number of a high value could be used here? Yep, that's pretty much it. Thanks for the feedback. Biff "luvthavodka" wrote in message ... Think I've answered my oen question....255 just represents the max number of characters in that cell? Hence any number of a high value could be used here? Thanks ever so much Biff!!!! "luvthavodka" wrote: Thanks Biff, thats great. Just for future reference, why 255? "Biff" 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,'Ma y 06'!$F$2:$F$65536)) That formula only works if the values are numeric. Since you're looking for "Becky" with is TEXT change this portion: MATCH(9.99999999999999E+307 Change to: MATCH(REPT("z",255) Note that this assumes column F ONLY contains TEXT values! Biff "Toppers" wrote in message ... Do you want to post me your file (toppers<atjohntopley.fsnet.co.uk). It might wait until tomorrow as it's getting latie here in the UK! "luvthavodka" wrote: Hmmmm... still returns #N/A...this has had me stumped for days!!!! "Toppers" wrote: 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 | |||
Linear Mode
