Trying to count number of unique entries in one column that match criteria in two other columns

[Gabriel Stern]

Hello all,
I've been unable to solve this problem (in Excel 2007) for several days and hope someone here can help. I've attached an example spreadsheet (the real one is much bigger), but maybe the "table" I've entered below will be enough info.

I'm trying to count the number of unique entries in the ECR Name Column that match certain criteria in the Product, Status, and Current Status columns.

So, for example, I'd like to count the number of unique entries in the ECR Name column for which:
- The entry in Prod column is "CC" --- and
- The entry in Status column is "Closed" --- and
- The entry in Current Status column is "Open"

In my simplification below, the result should be 3 because only rows 3, 9, and 17 meet the criteria. Row 18 should not be counted because line 17 and line 18 both share the same ECR Name "ECR-000899"
____________________________________________________
Prod ECR Name Status Current Status
_________________________________________________
2 CC ECR-000872 Closed Closed
3 CC ECR-000874 Closed Open
4 CC ECR-000875 Closed Closed
5 AA ECR-000876 Closed Closed
6 AA ECR-000876 Approved*(In-Process) Open
7 CC ECR-000878 Closed Closed
8 DD ECR-000879 Closed Closed
9 CC ECR-000883 Closed Open
10 CC ECR-000883 Approved*(In-Process) Open
11 AA ECR-000887 Closed Open
12 CC ECR-000888 Closed Closed
13 DD ECR-000890 Closed Closed
14 AA ECR-000891 Closed Open
15 DD ECR-000897 Closed Closed
16 AA ECR-000898 Closed Closed
17 CC ECR-000899 Closed Open
18 CC ECR-000899 Closed Open
19 CC ECR-000899 Approved*(Parked) Open

I sure hope someone can help!
Thanks,
Gabriel

 

[JoeMo]

There's probably a more elegant solution, but maybe this will help in the meantime.
In Excel 2007 select all the data and go to the ribbon Data/RemoveDuplicates. That will eliminate the duplicate record in row 18. Then process the remaining records as shown below.
Sheet6

*	A	B	C	D	E	F	G	H	I
1	Prod	ECR Name	Status	Curr Status	*	Prod	Status	Curr Status	Unique Ct
2	CC	ECR-000872	Closed	Closed	*	CC	Closed	Open	3
3	CC	ECR-000874	Closed	Open	*	*	*	*	*
4	CC	ECR-000875	Closed	Closed	*	*	*	*	*
5	AA	ECR-000876	Closed	Closed	*	*	*	*	*
6	AA	ECR-000876	Approved*(In-Process)	Open	*	*	*	*	*
7	CC	ECR-000878	Closed	Closed	*	*	*	*	*
8	DD	ECR-000879	Closed	Closed	*	*	*	*	*
9	CC	ECR-000883	Closed	Open	*	*	*	*	*
10	CC	ECR-000883	Approved*(In-Process)	Open	*	*	*	*	*
11	AA	ECR-000887	Closed	Open	*	*	*	*	*
12	CC	ECR-000888	Closed	Closed	*	*	*	*	*
13	DD	ECR-000890	Closed	Closed	*	*	*	*	*
14	AA	ECR-000891	Closed	Open	*	*	*	*	*
15	DD	ECR-000897	Closed	Closed	*	*	*	*	*
16	AA	ECR-000898	Closed	Closed	*	*	*	*	*
17	CC	ECR-000899	Closed	Open	*	*	*	*	*
18	CC	ECR-000899	Approved*(Parked)	Open	*	*	*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:77px;"><col style="width:82px;"><col style="width:74px;"><col style="width:28px;"><col style="width:36px;"><col style="width:49px;"><col style="width:74px;"><col style="width:68px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula I2 =SUMPRODUCT(--($A$2:$A$18=F2)*--($C$2:$C$18=G2)*--($D$2:$D$18=H2)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Gabriel Stern]

JoeMo,
I think your solution does work, but I'd have a hard time generating the many charts I have to create. The set of criteria I posed was only one of about 30 sets I need to create charts for, so I really need a formula that would allow me to do counts which include the criterion of ECR Name needing to be unique.
Thanks so much, though, for the help so far.
Gabriel

There's probably a more elegant solution, but maybe this will help in the meantime.
In Excel 2007 select all the data and go to the ribbon Data/RemoveDuplicates. That will eliminate the duplicate record in row 18. Then process the remaining records as shown below.
Sheet6

*	A	B	C	D	E	F	G	H	I
1	Prod	ECR Name	Status	Curr Status	*	Prod	Status	Curr Status	Unique Ct
2	CC	ECR-000872	Closed	Closed	*	CC	Closed	Open	3
3	CC	ECR-000874	Closed	Open	*	*	*	*	*
4	CC	ECR-000875	Closed	Closed	*	*	*	*	*
5	AA	ECR-000876	Closed	Closed	*	*	*	*	*
6	AA	ECR-000876	Approved*(In-Process)	Open	*	*	*	*	*
7	CC	ECR-000878	Closed	Closed	*	*	*	*	*
8	DD	ECR-000879	Closed	Closed	*	*	*	*	*
9	CC	ECR-000883	Closed	Open	*	*	*	*	*
10	CC	ECR-000883	Approved*(In-Process)	Open	*	*	*	*	*
11	AA	ECR-000887	Closed	Open	*	*	*	*	*
12	CC	ECR-000888	Closed	Closed	*	*	*	*	*
13	DD	ECR-000890	Closed	Closed	*	*	*	*	*
14	AA	ECR-000891	Closed	Open	*	*	*	*	*
15	DD	ECR-000897	Closed	Closed	*	*	*	*	*
16	AA	ECR-000898	Closed	Closed	*	*	*	*	*
17	CC	ECR-000899	Closed	Open	*	*	*	*	*
18	CC	ECR-000899	Approved*(Parked)	Open	*	*	*	*	*

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula I2 =SUMPRODUCT(--($A$2:$A$18=F2)*--($C$2:$C$18=G2)*--($D$2:$D$18=H2)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Aladin Akyurek]

Hello all,
I've been unable to solve this problem (in Excel 2007) for several days and hope someone here can help. I've attached an example spreadsheet (the real one is much bigger), but maybe the "table" I've entered below will be enough info.

I'm trying to count the number of unique entries in the ECR Name Column that match certain criteria in the Product, Status, and Current Status columns.

So, for example, I'd like to count the number of unique entries in the ECR Name column for which:
- The entry in Prod column is "CC" --- and
- The entry in Status column is "Closed" --- and
- The entry in Current Status column is "Open"

In my simplification below, the result should be 3 because only rows 3, 9, and 17 meet the criteria. Row 18 should not be counted because line 17 and line 18 both share the same ECR Name "ECR-000899"
____________________________________________________
Prod ECR Name Status Current Status
_________________________________________________
2 CC ECR-000872 Closed Closed
3 CC ECR-000874 Closed Open
4 CC ECR-000875 Closed Closed
5 AA ECR-000876 Closed Closed
6 AA ECR-000876 Approved*(In-Process) Open
7 CC ECR-000878 Closed Closed
8 DD ECR-000879 Closed Closed
9 CC ECR-000883 Closed Open
10 CC ECR-000883 Approved*(In-Process) Open
11 AA ECR-000887 Closed Open
12 CC ECR-000888 Closed Closed
13 DD ECR-000890 Closed Closed
14 AA ECR-000891 Closed Open
15 DD ECR-000897 Closed Closed
16 AA ECR-000898 Closed Closed
17 CC ECR-000899 Closed Open
18 CC ECR-000899 Closed Open
19 CC ECR-000899 Approved*(Parked) Open

I sure hope someone can help!
Thanks,
Gabriel

F2: CC
G2: Closed
H2: Open

I2, control+shift+enter, not just enter, and copy down for other triplets...

=SUM(IF(FREQUENCY(IF($B$2:$B$19<>"",IF($A$2:$A$19=$F2,IF($C$2:$C$19=$G2,
  IF($D$2:$D$9=$H2,MATCH("~"&$B$2:$B$19,$B$2:$B$19&"",0))))),
   ROW($B$2:$B$19)-ROW($B$2)+1),1))

If there no special meaning chars like < are expected around the target entries, the "~"& and &"" bits can be removed from the formula:

=SUM(IF(FREQUENCY(IF($B$2:$B$19<>"",IF($A$2:$A$19=$F2,IF($C$2:$C$19=$G2,
  IF($D$2:$D$9=$H2,MATCH($B$2:$B$19,$B$2:$B$19,0))))),
   ROW($B$2:$B$19)-ROW($B$2)+1),1))

 

[Gabriel Stern]

Aladin,
Wonderful. Thank you very much. Your formula works perfectly. Is it possible you could provide a solution for the "follow up" problem.
In addition to the columns I described, there's one more column that has the number of days spent in various states. I've used numbers here that will make the results easy to check.
So the table is below:
and I need to get the Average number of days for the same rows that were counted with the formula you provided me namely:
Average for Days column if:
- The entry in Prod column is "CC" --- and
- The entry in Status column is "Closed" --- and
- The entry in Current Status column is "Open"
Here, I'd expect to get
20 (for row 3)
5 (for row 9)
5 (for row 17)
Average = 10
Row 18, again would not have been tallied as part of the average.
Prod ECR Name Status Current Status Days
_________________________________________________
2 CC ECR-000872 Closed Closed 10.0
3 CC ECR-000874 Closed Open 20.0
4 CC ECR-000875 Closed Closed 10.0
5 AA ECR-000876 Closed Closed 5.0
6 AA ECR-000876 Approved*(In-Process) Open 5.0
7 CC ECR-000878 Closed Closed 5.0
8 DD ECR-000879 Closed Closed 5.0
9 CC ECR-000883 Closed Open 5.0
10 CC ECR-000883 Approved*(In-Process) Open 20.0
11 AA ECR-000887 Closed Open 20.0
12 CC ECR-000888 Closed Closed 20.0
13 DD ECR-000890 Closed Closed 20.0
14 AA ECR-000891 Closed Open 10.0
15 DD ECR-000897 Closed Closed 10.0
16 AA ECR-000898 Closed Closed 10.0
17 CC ECR-000899 Closed Open 5.0
18 CC ECR-000899 Closed Open 10.0
19 CC ECR-000899 Approved*(Parked) Open 20.0

F2: CC
G2: Closed
H2: Open

I2, control+shift+enter, not just enter, and copy down for other triplets...

=SUM(IF(FREQUENCY(IF($B$2:$B$19<>"",IF($A$2:$A$19=$F2,IF($C$2:$C$19=$G2,
  IF($D$2:$D$9=$H2,MATCH("~"&$B$2:$B$19,$B$2:$B$19&"",0))))),
   ROW($B$2:$B$19)-ROW($B$2)+1),1))

If there no special meaning chars like < are expected around the target entries, the "~"& and &"" bits can be removed from the formula:

=SUM(IF(FREQUENCY(IF($B$2:$B$19<>"",IF($A$2:$A$19=$F2,IF($C$2:$C$19=$G2,
  IF($D$2:$D$9=$H2,MATCH($B$2:$B$19,$B$2:$B$19,0))))),
   ROW($B$2:$B$19)-ROW($B$2)+1),1))

 

[Aladin Akyurek]

Aladin,
Wonderful. Thank you very much. Your formula works perfectly.

You are welcome.

Is it possible you could provide a solution for the "follow up" problem.
In addition to the columns I described, there's one more column that has the number of days spent in various states. I've used numbers here that will make the results easy to check.
So the table is below:
and I need to get the Average number of days for the same rows that were counted with the formula you provided me namely:
Average for Days column if:
- The entry in Prod column is "CC" --- and
- The entry in Status column is "Closed" --- and
- The entry in Current Status column is "Open"
Here, I'd expect to get
20 (for row 3)
5 (for row 9)
5 (for row 17)
Average = 10
Row 18, again would not have been tallied as part of the average.
Prod ECR Name Status Current Status Days
_________________________________________________
2 CC ECR-000872 Closed Closed 10.0
3 CC ECR-000874 Closed Open 20.0
4 CC ECR-000875 Closed Closed 10.0
5 AA ECR-000876 Closed Closed 5.0
6 AA ECR-000876 Approved*(In-Process) Open 5.0
7 CC ECR-000878 Closed Closed 5.0
8 DD ECR-000879 Closed Closed 5.0
9 CC ECR-000883 Closed Open 5.0
10 CC ECR-000883 Approved*(In-Process) Open 20.0
11 AA ECR-000887 Closed Open 20.0
12 CC ECR-000888 Closed Closed 20.0
13 DD ECR-000890 Closed Closed 20.0
14 AA ECR-000891 Closed Open 10.0
15 DD ECR-000897 Closed Closed 10.0
16 AA ECR-000898 Closed Closed 10.0
17 CC ECR-000899 Closed Open 5.0
18 CC ECR-000899 Closed Open 10.0
19 CC ECR-000899 Approved*(Parked) Open 20.0

What about row 18? We have at 17 and 18 the same record except for value!

 

[Gabriel Stern]

Right...18 should not be tallied since it has the same entry in the ECR Name column, and I'm trying.......
oh.....
I see a problem in what I'm even asking you to do.
I haven't thought this out so well.
I'm afraid I can't even think straight after so much effort... It's literally been days. I'll take a breather and get back to you with a more reasonable analysis of what I want.
Gabriel

 

[Gabriel Stern]

Well, yes. I was asking for something I don't need, and that doesn't make any sense. Really I want to count all entries in the Days column given criteria for Columns A and C. I don't care if the entries in Column B are unique. This makes it a simple AVERAGEIFS formula that even I was able to come up with.
In any case, I'm in awe of you seasoned board members. Honestly.
Thanks,
Gabriel

 

[novel]

Here is a message box version with a function that I already have that finds unique instances in a column and the code for the message box I got from here: Contents of Array, MsgBox and Looping - Access World Forums

This Calls the function and outputs the message box

Sub NameCount()
Dim sNames As Variant
Dim i As Integer
Dim strHold As String
Dim strMsg As String


sNames = GetNumbers("Sheet1", 3) 'sheet where the info is and column number for ECR


 
For i = 0 To UBound(sNames)
    strHold = strHold & sNames(i) & vbCrLf ' this is to list the numbers found
Next
 
Select Case i
   Case 1
      strMsg = i & " unique name:"
   Case Is > 1
 
      strMsg = i & " unique names:"
   Case Else
      strMsg = "There are no elements in the array."
End Select
 
MsgBox strMsg & vbCrLf & strHold 'does have a character limit so if you have large outputs they could be cutoff




End Sub

This finds the unique numbers based on conditions

Function GetNumbers(s As String, Colnum As Integer) As Variant
Dim n() As Variant
Dim rng As Range
Dim Lastrow As Long


ReDim n(0 To 0) As Variant
    If Application.CountA(n) = 0 Then
        blDimensioned = False
    End If
    With Sheets(s)
        Lastrow = .Cells(Rows.Count, Colnum).End(xlUp).Row
        Set rng = .Range(.Cells(2, Colnum), .Cells(Lastrow, Colnum))
        For Each e In rng
            Check1 = .Cells(e.Row, 2).Value 'ECR
            Check2 = .Cells(e.Row, 4).Value 'Status
            Check3 = .Cells(e.Row, 5).Value 'Current
            If Check1 = "CC" And Check2 = "Closed" And Check3 = "Open" Then
                If IsError(Application.Match(e, n, 0)) Then
                    If blDimensioned = True Then
                        ReDim Preserve n(0 To UBound(n) + 1) As Variant
                    Else
                        blDimensioned = True
                    End If
                    n(UBound(n)) = e
                End If
            End If
        Next e
        End With
GetNumbers = n()
End Function

 

[Aladin Akyurek]

Well, yes. I was asking for something I don't need, and that doesn't make any sense. Really I want to count all entries in the Days column given criteria for Columns A and C. I don't care if the entries in Column B are unique. This makes it a simple AVERAGEIFS formula that even I was able to come up with.
In any case, I'm in awe of you seasoned board members. Honestly.
Thanks,
Gabriel

No problem. Good to know that you sorted it out. As you probably know, you can invoke a formula with CountIfs for doing a multi-conditional count.