Count Unique Values in Range with 3 Criteria

[randolphoralph]

I am trying to use the formula below to count unique values in a range.

=SUM(IF(FREQUENCY(MATCH(Data!Q2:Q434289,Data!Q2:Q434289,0),MATCH(Data!Q2:Q434289,Data!Q2:Q434289,0))>0,1))

The problem I am having is that I need to count the unique values in the range only when the follow is true.

Data!$A$2:$A$434289=$A3

Data!$F$2:$F$434289=$A$1

Data!$H$2:$H$434289=$B$1

I have tried to add this into the formula and can not seem to get it to work.

 

[T. Valko]

I am trying to use the formula below to count unique values in a range.

=SUM(IF(FREQUENCY(MATCH(Data!Q2:Q434289,Data!Q2:Q434289,0),MATCH(Data!Q2:Q434289,Data!Q2:Q434289,0))>0,1))

The problem I am having is that I need to count the unique values in the range only when the follow is true.

Data!$A$2:$A$434289=$A3

Data!$F$2:$F$434289=$A$1

Data!$H$2:$H$434289=$B$1

I have tried to add this into the formula and can not seem to get it to work.

This will take "forever" to calculate.

=SUM(IF(FREQUENCY(IF(Data!$A$2:$A$434289=$A3,IF(Data!$F$2:$F$434289=$A$1,IF(Data!$H$2:$H$434289=$B$1,MATCH(Data!Q2:Q434289,Data!Q2:Q434289,0)))),ROW(Data!Q2:Q434289)-ROW(Data!Q2)+1),1))

 

[T. Valko]

This will take "forever" to calculate.

=SUM(IF(FREQUENCY(IF(Data!$A$2:$A$434289=$A3,IF(Data!$F$2:$F$434289=$A$1,IF(Data!$H$2:$H$434289=$B$1,MATCH(Data!Q2:Q434289,Data!Q2:Q434289,0)))),ROW(Data!Q2:Q434289)-ROW(Data!Q2)+1),1))

That's array entered.

 

[WinteE]

Try :

=SUMPRODUCT(($A$2:$A$434289=$A3)*($F$2:$F$434289)*($H$2:$H$434289))

Success

 

[randolphoralph]

Biff...you were not kidding about the formula taking "forever" to calculate. I have about 200 cells on 3 sheets that I need to use this formula on. I am thinking that this may not be the best solution.

 

[T. Valko]

Biff...you were not kidding about the formula taking "forever" to calculate. I have about 200 cells on 3 sheets that I need to use this formula on. I am thinking that this may not be the best solution.

That's a lot of rows of data and with several conditions = good luck!

Excel sorely needs a built-in function for counting uniques.

 

[randolphoralph]

Well here is my solution....copy the data to a new sheet. Delete only the values I want to count and the criteria I want to look at. Then remove duplicates. Then use SUMPRODUCT to count the values with the duplicates removed.