Count Duplicate Rows with Conditions

[wolf11]

Hi, Anyone who can help me with a formula for counting unique rows with condition

1) it must count only the unique task number
2) the counted unique task number must have task type P
result: 3


( optional ) Count Unique Task Number must have Task Type P with Name D
result: 1


I know the formula to compute for unique text only in one row but what bout with conditions?
Would really appreciate help

=SUMPRODUCT((B3:B12<>"")/COUNTIF(B3:B12,B3:B12&""))


[TABLE="width: 329"]
<tbody>[TR]
[TD][TABLE="class: outer_border, width: 500, align: left"]
<tbody>[TR]
[TD]A1[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Task Number[/TD]
[TD]Task Type[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]T1[/TD]
[TD]C[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]T2[/TD]
[TD]P[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]T2[/TD]
[TD]P[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]T3[/TD]
[TD]C[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]T4[/TD]
[TD]C[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]T5[/TD]
[TD]C[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]T6[/TD]
[TD]P[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]T6[/TD]
[TD]P[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]T7[/TD]
[TD]P[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]T8[/TD]
[TD]C[/TD]
[TD]L[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Robert Mika]

Excel 2010
	A	B	C	D	E
1	Task Number	Task Type	Name
2	T1	C	M	3	1
3	T2	P	D
4	T2	P	D
5	T3	C	M
6	T4	C	L
7	T5	C	M
8	T6	P	R
9	T6	P	R
10	T7	P	R
11	T8	C	L
Sheet2

Cell Formulas
Range	Formula
D2	{=SUM(IF(FREQUENCY(IF(B2:B11="P",MATCH("~"&A2:A11,A2:A11&"",0)),ROW(A2:A11)-ROW(A2)+1),1))}
E2	{=SUM(IF(FREQUENCY(IF(B2:B11="P",IF($C$2:$C$11="D",MATCH("~"&A2:A11,A2:A11&"",0))),ROW(A2:A11)-ROW(A2)+1),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Aladin Akyurek]

Hi, Anyone who can help me with a formula for counting unique rows with condition

1) it must count only the unique task number
2) the counted unique task number must have task type P
result: 3


( optional ) Count Unique Task Number must have Task Type P with Name D
result: 1


I know the formula to compute for unique text only in one row but what bout with conditions?
Would really appreciate help

=SUMPRODUCT((B3:B12<>"")/COUNTIF(B3:B12,B3:B12&""))


[TABLE="width: 329"]
<tbody>[TR]
[TD]
[TABLE="class: outer_border, width: 500, align: left"]
<tbody>[TR]
[TD]A1
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Task Number
[/TD]
[TD]Task Type
[/TD]
[TD]Name
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]T1
[/TD]
[TD]C
[/TD]
[TD]M
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]T2
[/TD]
[TD]P
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]T2
[/TD]
[TD]P
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]T3
[/TD]
[TD]C
[/TD]
[TD]M
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]T4
[/TD]
[TD]C
[/TD]
[TD]L
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]T5
[/TD]
[TD]C
[/TD]
[TD]M
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]T6
[/TD]
[TD]P
[/TD]
[TD]R
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]T6
[/TD]
[TD]P
[/TD]
[TD]R
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]T7
[/TD]
[TD]P
[/TD]
[TD]R
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]T8
[/TD]
[TD]C
[/TD]
[TD]L
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Control+shift+enter, not just enter...

1)

=SUM(IF(FREQUENCY(IF(B3:B12<>"",MATCH("~"&B3:B12,B3:B12&"",0)),
  ROW(B3:B12)-ROW(B3)+1),1))

2)

=SUM(IF(FREQUENCY(IF(B3:B12<>"",IF(C3:C12="P",
  MATCH("~"&B3:B12,B3:B12&"",0))),ROW(B3:B12)-ROW(B3)+1),1))

3)

=SUM(IF(FREQUENCY(IF(B3:B12<>"",IF(C3:C12="P",IF(D3:D12="D",
  MATCH("~"&B3:B12,B3:B12&"",0)))),ROW(B3:B12)-ROW(B3)+1),1))

if there are no special meaning chars around the entries in column B, you might want to remove the "~"& and &"" bits from the formulas.

 

[hurgadion]

Hi,
You can use the following code:

Sub Counting()
Dim i&, NoDupes As New Collection

On Error Resume Next
For i = 3 To 12
  If Cells(i, 3).Value = "P" Then 'And Cells(i, 4).Value = "D"
    NoDupes.Add 1, Cells(i, 2).Value
  End If
Next i

MsgBox NoDupes.Count
End Sub

Best regards.

 

[wolf11]

Thanks all!!! works excellent