Counting unique items given 2 criteria

[neb149]

I am trying to design a formula to count the number of unique values given 2 criteria.

<TABLE style="WIDTH: 228pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=303 border=0><COLGROUP><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3949" width=108><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3547" width=97><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584" width=98><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 81pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=108 height=17>A

</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 73pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=97>B</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 74pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=98>C</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Fraud</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Complete</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">11111</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Fraud</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Complete</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">11111</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Fraud</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Complete</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">11121</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>No Fraud

</TD><TD class=xl65 id=td_post_2397543 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Complete</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">11222</TD></TR></TBODY></TABLE>

I would like to know how many Frauds have completed given each individual occurance of C. The answer I am looking for, given this example, would be 2. Please help.

 

[JoeMo]

I am trying to design a formula to count the number of unique values given 2 criteria.

Excel Workbook
	B	C
Fraud	Complete	11111
Fraud	Complete	11111
Fraud	Complete	11121
No Fraud	Complete	11222
Sheet

I would like to know how many Frauds have completed given each individual occurance of C. The answer I am looking for, given this example, would be 2. Please help.

The column C value you want to use as a criterion is in cell E1:

Excel Workbook
	A	B	C	D	E
1	Fraud	Complete	11111		11111
2	Fraud	Complete	11111		2
3	Fraud	Complete	11122
4	No Fraud	Complete	11222
Sheet9

 

[neb149]

Not quite what I am looking for, let me rephrase.
I will be constantly adding data to this and need it to update whenever a new line of data is inputted.
I need it to exclude duplicates in column C with the criteria of B = Complete and A= Fraud. In your example, I am just getting the result for "11111" if A = Fraud and B = Complete. I need it to count each additional input of a unique value in C given those 2 criteria. I hope this makes sense.

 

[Matty]

If I understand correctly, perhaps:

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 66px"><COL style="WIDTH: 68px"><COL style="WIDTH: 47px"><COL style="WIDTH: 19px"><COL style="WIDTH: 93px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Fraud</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Complete</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">11111</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD>Unique Count</TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Fraud</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Complete</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">11111</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Fraud</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Complete</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">11111</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD></TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Fraud</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Complete</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">11111</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD></TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Fraud</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Complete</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">11122</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">No Fraud</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Complete</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">11222</TD><TD></TD><TD></TD></TR></TBODY></TABLE>

<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>E2</TD><TD>{=SUM(IF(FREQUENCY(IF(A$1:A$6="Fraud",IF(B$1:B$6="Complete",MATCH("~"&C$1:C$6,C$1:C$6&"",0))),ROW(C$1:C$6)-ROW(C$1)+1),1))}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

</TD></TR></TBODY></TABLE>

Matty

 

[JoeMo]

If I understand correctly, perhaps:

Excel Workbook
	A	B	C	D	E
1	Fraud	Complete	11111		Unique Count
2	Fraud	Complete	11111		2
3	Fraud	Complete	11111
4	Fraud	Complete	11111
5	Fraud	Complete	11122
6	No Fraud	Complete	11222
Sheet

Matty

Nice work Matty. I've modified your solution slightly so data added beyond the current range is handled too. E1 is a count of rows with data, F1 is Matty's formula modified slightly.

Excel Workbook
	A	B	C	D	E	F
1	Fraud	Complete	12345		9	5
2	Fraud	Complete	11111
3	Fraud	Complete	11111
4	No Fraud	Complete	11222
5	Fraud	Complete	11222
6	Fraud	Complete	11111
7	Fraud	Complete	11101
8	Fraud	Complete	11102
9	No Fraud	Complete	11103
Sheet9

 

[Aladin Akyurek]

Nice work Matty. I've modified your solution slightly so data added beyond the current range is handled too. E1 is a count of rows with data, F1 is Matty's formula modified slightly.
[...]

Not such a good idea to have so many (volatile) INDIRECTs... Moreover, the resulting formula would not be robust.

Here is another option if you insist on having it on a dynamic footing:

Let's assume Sheet1, with the current range of A1:C7...

<TABLE style="WIDTH: 157pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=210><COLGROUP><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3043" width=86><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2588" width=73><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1820" width=51><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 64pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=86>Observation</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 55pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=73>Status</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=51>Code</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 64pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=86>Fraud</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 55pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=73>Complete</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 38pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=51>11111</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 64pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=86>Fraud</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 55pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=73>Complete</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 38pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=51>11111</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 64pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=86>Fraud</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 55pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=73>Complete</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 38pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=51>11111</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 64pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=86>Fraud</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 55pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=73>Complete</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 38pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=51>11111</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 64pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=86>Fraud</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 55pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=73>Complete</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 38pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=51>11122</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 64pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=86>No Fraud</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 55pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=73>Complete</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 38pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=51>11222</TD></TR></TBODY></TABLE>

Define Size (by means of Insert|Name|Define or Formulas|Define Name) as referring to:

=MATCH(REPT("z",255),Sheet1!$A:$A)-ROW(Sheet1!$A$2)-1

Define Observation as referring to:

=OFFSET(Sheet1!$A$2,0,0,Size)

with Status as:

=OFFSET(Sheet1!$B$2,0,0,Size)

and Code as:

=OFFSET(Sheet1!$C$2,0,0,Size)

The current range formula, which must be confirmed with control+shift+enter...

[1]

=SUM(IF(FREQUENCY(IF(A$2:A$7="Fraud",IF(B$2:B$7="Complete",
    IF($C$2:$C$7<>"",MATCH("~"&C$2:C$7,C$2:C$7&"",0)))),
      ROW(C$2:C$7)-ROW(C$2)+1),1))

would now become:

[2]

=SUM(IF(FREQUENCY(IF(Observation="Fraud",IF(Status="Complete",
    IF(Code<>"",MATCH("~"&Code,Code&"",0)))),
      ROW(Code)-ROW(INDEX(Code,1,1))+1),1))

Note that these formulas also test for whether C2:C7 is blank/empty for such must
not be counted as a distinct item.