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?
|