Thank you for looking at this formula. I hope I have helped answer your
question.
Say a supplier has 5 parts that they need to provide a price for and they
only provide a quote on 1 of them, the way the formula works now is that once
there is a price any one of those parts the formula counts that supplier as
having fulfilled their obligation when they realy haven't. I'm trying to see
if the formula can be written to ignore that supplier unless all the parts
have been quoted.

Supplier Price
abc 1
abc 5
abc
def 10
def 25
would like the formula to say 1 supplier left open
Total suppliers = 2 (differnt formula)
Suppliers fulfilling their quotes = 1
Supliers left to quote = 1

Currently the formula equals
Suppliers fulfilling their quotes = 2




"Bob Phillips" wrote:

I don't quite understand. If F or P are blank, it doesn't count as an
instance, so what are blank in your examples that get counted?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Joe Gieder" wrote in message
...
First thank you for looking at this and sorry for the long post and
formula,
if it's not clear please let me know.

I'm trying to figure out how to re-write the below array formula:

=COUNT(1/FREQUENCY(IF(LEFT('[Spares Quotes List V22
26Apr2007.xls]All'!$A$2:$A$5003,4)=RIGHT($A41,4),IF('[Spares Quotes List
V22
26Apr2007.xls]All'!$F$2:$F$5003<"",IF('[Spares Quotes List V22
26Apr2007.xls]All'!$P$2:$P$5003<"",MATCH('[Spares Quotes List V22
26Apr2007.xls]All'!$P$2:$P$5003,'[Spares Quotes List V22
26Apr2007.xls]All'!$P$2:$P$5003,0)))),ROW('[Spares Quotes List V22
26Apr2007.xls]All'!$P$2:$P$5003)-ROW('[Spares Quotes List V22
26Apr2007.xls]All'!$P$2)+1))

What this formula does is compares the id number in column A on both
worksheets and then looks at column F to make sure it's not blank then
counts
all the unique supplier names in column P that aren't blank. The problem I
have just run into is say the supplier has 3 parts and only prices 2 of
them,
the formula counts the supplier as complete when they aren't. Can this
formula be written a different way to still only count the unique supplier
names but have the criteria be to count them only if all parts are quoted?

Thank you in advance for your help and sorry for the long post.

Joe