Count Unique Values on Multiple Criteria

[mgskater12]

I am looking to count unique values in my sheet based on three criteria.

Column B contains a list of product types (text)
Column D contains a list of formulas (text)
Column E contains a list of units made (#)

I want to count the unique values in column D when units made (column E) are greater than 0 and the product type (column B) is equal to cell A2.

Thanks

 

[gaz_chops]

Welcome to the forum.

Look at countifs

 

[mgskater12]

Countifs doesn't only count unique values. It will count all values in column D and I only want to count unique values.

 

[gaz_chops]

[TABLE="width: 130"]
<!--StartFragment--> <colgroup><col width="65" span="2" style="width:65pt"> </colgroup><tbody>[TR]
  [TD="width: 65, align: right"]1[/TD]
  [TD="width: 65, align: right"]5[/TD]
 [/TR]
 [TR]
  [TD="align: right"]2[/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD="align: right"]3[/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD="align: right"]3[/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD="align: right"]4[/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD="align: right"]5[/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD="align: right"]5[/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD="align: right"]3[/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD="align: right"]1[/TD]
  [TD][/TD]
 [/TR]
<!--EndFragment--></tbody>[/TABLE]

=SUM(1/COUNTIF(A1:A9,A1:A9))

 

[63falcondude]

Try =SUMPRODUCT(1/COUNTIFS($B$2:$B$4,$A$2,$D$2:$D$4,$D$2:$D$4,$E$2:$E$4,">0"))

Changing the 4 to whatever row your data ends on

 

[mgskater12]

Try =SUMPRODUCT(1/COUNTIFS($B$2:$B$4,$A$2,$D$2:$D$4,$D$2:$D$4,$E$2:$E$4,">0"))

Changing the 4 to whatever row your data ends on

How do I get the "#div/0!" error to be ignored for rows that have no match to all three criteria?

 

[63falcondude]

How do I get the "#div/0!" error to be ignored for rows that have no match to all three criteria?

You're right, my mistake. I didn't test it much. Working on it.

 

[63falcondude]

Try =SUM(--(FREQUENCY(IF(($B$2:$B$4=$A$2)*($E$2:$E$4>0),MATCH($D$2:$D$4,$D$2:$D$4,0)),ROW($D$2:$D$4)-ROW($D$2)+1)>0)) Ctrl Shift Enter

 

[RicardoCubed]

I needed this too. Awesome works great!!!!!

 

[mgskater12]

Try =SUM(--(FREQUENCY(IF(($B$2:$B$4=$A$2)*($E$2:$E$4>0),MATCH($D$2:$D$4,$D$2:$D$4,0)),ROW($D$2:$D$4)-ROW($D$2)+1)>0)) Ctrl Shift Enter

Works perfect!

Thanks!