Counting Certain Unique Values

[Vispon]

Hey folks,

I was hoping someone here miught be able to suggest a formula to solve a problem I'm having.

At the moment I have a large worksheet listing various pieces of equipment, their maintenance scedules and such like. What I want to do is count how many unique values there are (which would be decided by the barcode column) which DON'T have a status of Rehab or Defect.

I can work out formulas to count rehab\defect or count uinique values but for the life of me I can't work out a formula that will do both.

Any help would be very much appreciated.

[oldchippy]

Hi,

Welcome to the forum, you're nearly there with counting those, you just need to take those away from the total count

[Vispon]

thanks for the welcome

Unfortunately thought the solutions not quite that simple. Some pieces of equipment have 2 entries (both of which will say rehab, defect, readiness ot whatever) so if i just counted defect or rehab i'd end up with two many.

What I need to do is count the number of unique instances of rehab or defect (their uniqueness decided by the value in column A).

[martindwilson]

are there any instances where there are all 3 ie with Rehab with Defect. and without?

[Vispon]

Nope, ther are 17 columns in the workbook at the moment (including barcode, description, bay\rack etc) but the status will only ever be one value.

i was wondering if I could somehow combine these two formulas (which I use elsewhere) and then take it away from the total of unique items (which has already been worked out).

=SUM(IF(FREQUENCY(MATCH(B3:B850,B3:B850,0),MATCH(B3:B850,B3:B850,0))>0,1))

=COUNTIF(P3:P850, "Defect")+COUNTIF(P3:P850, "Rehab")

any ideas on how I could combine them?

[martindwilson]

Some pieces of equipment have 2 entries (both of which will say rehab, defect, readiness ot whatever)

so if its rehab and readines does that get counted or not? do you see the problem? also how is the barcode entered in what format? i think you need to post an example
take

123456 Rehab
123456 Defect
123456
456789
456789
456789
654321 Defect
654321 Rehab

so is the count unique of above 1 or 2 ie 456789 is th only number not to have Rehab/Defect
so count is one
or 123456 is counted as one instance does not have Rehab/Defect
so count is 2

[Vispon]

Everything unique value would need to be counted Exept Rehab and defects so,

example:
OS14557 Rehab
OS14557 Rehab
OS75009 Ready
OS02221 Defect
OS75565 Ready
OS75565 Ready
OS11155 Project
OS16234
OS56643 TBC

So in this example I would want a value of 5 to be returned.

This being so I thinnk I've sorted out a work around

=SUMPRODUCT(((B3:B850<>"")*((K3:K850="Rehab")+(K3:K850="Defect")))/COUNTIF(B3:B850,B3:B850&""))

by using this formula I can count the number of unique rehab + defect instances and then take it away from the total number of unique items. It's a little bit untidy but seems to work well.

thanks for your help guys.