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&""))

A1 B C D 2 Task Number Task Type Name 3 T1 C M 4 T2 P D 5 T2 P D 6 T3 C M 7 T4 C L 8 T5 C M 9 T6 P R 10 T6 P R 11 T7 P R 12 T8 C L <tbody> </tbody>

<tbody>
</tbody>

 

[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&""))

A1 B C D 2 Task Number Task Type Name 3 T1 C M 4 T2 P D 5 T2 P D 6 T3 C M 7 T4 C L 8 T5 C M 9 T6 P R 10 T6 P R 11 T7 P R 12 T8 C L <tbody> </tbody>

<tbody>
</tbody>

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