Find when this many unique items have been replaced?

  • Thread starter Thread starter Ed from AZ
  • Start date Start date
E

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
 
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
 
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
 
does this do what you want?

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

Jim

Ed from AZ said:
Tell us all the details and we can try to be more helpful
Click to expand...

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
 
Back
Top