Find when this many unique items have been replaced?

[Ed from AZ]

I have a list of items we've replaced. Some of them have been
replaced several times; others may not have ever been replaced at
all. I need to know when a certain number of unique items have been
replaced.

I'm thinking I can do it with a column of all the unique item
numbers. In the next column is a formula (COUNTIF ??) that will
return something if that item number shows up in the replacements
list. Then another formula counts the number of returns and flags me
when I reach a specific target number.

Is there a better way to handle this? Or does that look like the best
way to go?

Ed

 

[Bernard Liengme]

Do you have two lists?
One a list of codes of items that were replaced
the second a list all unique item codes?

What does the second list look like?
Does it have code in one column and a count in another?
The COUNTIF will work

Or is it just a list of codes
The SUMPRODUCT will sove your problem

Tell us all the details and we can try to be more helpful
best wishes

 

[Ed from AZ]

Tell us all the details and we can try to be more helpful

Well - you asked! 8>)

This is a report generated from a database. I think it outputs
everything as text. It's coming out in XL2002 format supposedly,
although I run a macro to do a bit of cleanup and save it as XL2003-
compatible. I have XL2007 on my machine.

Of the several columns, the ones I am interested in would look
something like:
Store Item No. Item ID Date Repl
A 11 S
A 11 M 8/6/2009
A 11 L
A 11 XL 8/6/2009
A 12 S
A 12 M 8/6/2009
A 12 L 8/6/2009
A 12 XL
B 11 S 8/6/2009
B 11 M 8/6/2009
B 11 L 8/6/2009
B 11 XL
B 12 S
B 12 M 8/6/2009
B 12 L 8/6/2009
B 12 XL

So I need to know when three of the four Item 12 have been replaced at
Store B. (Not the date - just the count of three - or an IF
(Count12>2, "Hey!","")) It could be any three of the four. And of
course I would duplicate that to monitor all the other sets from the
other groups.

Does that help?
Ed

 

[Jim]

does this do what you want?

=IF(AND(A12="b",COUNTA(A1212>2)),"hey!","")

Jim

Tell us all the details and we can try to be more helpful

Well - you asked! 8>)

This is a report generated from a database. I think it outputs
everything as text. It's coming out in XL2002 format supposedly,
although I run a macro to do a bit of cleanup and save it as XL2003-
compatible. I have XL2007 on my machine.

Of the several columns, the ones I am interested in would look
something like:
Store Item No. Item ID Date Repl
A 11 S
A 11 M 8/6/2009
A 11 L
A 11 XL 8/6/2009
A 12 S
A 12 M 8/6/2009
A 12 L 8/6/2009
A 12 XL
B 11 S 8/6/2009
B 11 M 8/6/2009
B 11 L 8/6/2009
B 11 XL
B 12 S
B 12 M 8/6/2009
B 12 L 8/6/2009
B 12 XL

So I need to know when three of the four Item 12 have been replaced at
Store B. (Not the date - just the count of three - or an IF
(Count12>2, "Hey!","")) It could be any three of the four. And of
course I would duplicate that to monitor all the other sets from the
other groups.

Does that help?
Ed