SUMPRODUCT/COUNTIF

[Guest]

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?

 

[Guest]

Are all the column sizes the same i.e 2 to 9999?

 

[Guest]

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

 

[Guest]

You need to ensure Column F is the same size as the others whose length is
defined by the INDEX function.

 

[Guest]

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?

 

[Guest]

What happens with SUMPRODUCT just on colum F

=SUMPRODUCT(--(Underwriter_May="Becky")

OR

=SUMPRODUCT(--(Underwriter_May$J2)

 

[Guest]

Hmmmm... still returns #N/A...this has had me stumped for days!!!!

 

[Guest]

Do you want to post me your file (toppers<at>johntopley.fsnet.co.uk). It
might wait until tomorrow as it's getting latie here in the UK!

 

[Biff]

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

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

 

[Biff]

P.S.

Also note, just because you're using a dynamic range each range MUST still
end on the same row.

Biff

 

[Guest]

Thanks Biff, thats great. Just for future reference, why 255?

 

[Guest]

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

 

[Biff]

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