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

 

[Gabriel Stern]

Yes...that's true...the Countifs give's me a multi-conditional count, but your formula provided the ability to select only unique entries in one of the columns. The Averageifs works fine for giving me conditional averages (since I don't need to take uniqueness into account.
Thanks again,
Gabriel

 

[Aladin Akyurek]

Yes...that's true...the Countifs give's me a multi-conditional count, but your formula provided the ability to select only unique entries in one of the columns. The Averageifs works fine for giving me conditional averages (since I don't need to take uniqueness into account.

Right. Thanks for clarifying.

Thanks again,
Gabriel

You are welcome.