Counting unique values with specific criteria

[Vispon]

Hey folks,

I was hoping someone here might 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 schedules and such like. What I want to do is count the total number of entries with a unique barcode (column B) which also have a status of rehab or defect.

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

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

Any help on achieving this would be very much appreciated.

 

[Sandeep Warrier]

Hi & welcome to the Board!

Does this help?

Excel Workbook
	A	B	C	D
1	OS14557	Rehab		3
2	OS14557	Rehab
3	OS75565	Ready
4	OS02221	Defect
5	OS75565	Ready
6	OS11155	defect
7	OS16234
8	OS56643	TBC
Sheet1

Excel 2003

Cell Formulas
Range	Formula
D1	=SUMPRODUCT((($A$1:$A$8<>"")*(($B$1:$B$8="Rehab")+($B$1:$B$8="Defect")))/COUNTIF($A$1:$A$8,$A$1:$A$8&""))

<input id="gwProxy" type="hidden"><!--Session data--><input *******="if(typeof(jsCall)=='function'){jsCall();}else{setTimeout('jsCall()',500);}" id="jsProxy" type="hidden">

 

[machopicho]

<TABLE style="WIDTH: 337pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=449><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 241pt; mso-width-source: userset; mso-width-alt: 11739" width=321><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21 width=64>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 241pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=321>C</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>OS14557

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Rehab</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>OS14557</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Rehab</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>=Countif(B1:B8;"Rehab")+Countif(B1:B8;"Defect")</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>OS75565</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Ready</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>OS02221</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Defect</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>OS75565</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Ready</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>OS11155</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>defect</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>OS16234</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>OS56643

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2373052 class=xl67>TBC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD></TR></TBODY></TABLE>

 

[Hooi]

Hey folks,

I was hoping someone here might 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 schedules and such like. What I want to do is count the total number of entries with a unique barcode (column B) which also have a status of rehab or defect.

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

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

Any help on achieving this would be very much appreciated.

Put a header on the column which contain Rehab,Defect,TBC and Ready...
Let name the header as "Header"...Then find another 3 column to insert this criteria:
Header
="=Rehab"
="=Defect"

enter the formula which the cell u wanted to returned value with this formula
=dcount((Database),(blank it),(criteria))

 

[Vispon]

Brilliant thanks Sandeep, solved in one, thanks very much.

Thanks for the reply as well machopicho but am I not right in saying that formula would not discount duplicates?

 

[Aladin Akyurek]

A1:B8 houses the relevant sample.

C1, control+shift+enter, not just enter...

=SUM(IF(FREQUENCY(IF(A1:A8<>"",
   IF(ISNUMBER(MATCH(B1:B8,{"rehab","defect"},0)),
    MATCH("~"&A1:A8,A1:A8&"",0))),ROW(A1:A8)-ROW(A1)+1),1))

 

[Fazza]

hi, Vispon

With a large amount of data you might find the array formulas slow. If so, there are other approaches. One is a query table with SQL as below (untested). Set up via menu ALT-D-D-N and follow the wizard. For ease of implementation, use a non-dynamic defined name on the data before starting the wizard.

HTH, Fazza

SELECT DISTINCT COUNT(*) AS [MyCount]
FROM MyData
WHERE MyData.STATUS IN ('Defect', 'Rehab')