Count Unique Values on Multiple Criteria

G

gaz_chops

Well-known Member

Joined

Apr 29, 2003

Messages

6,485

Platform

1. MacOS

• Apr 24, 2017

• #2

Welcome to the forum.

Look at countifs

 

Upvote 0

M

mgskater12

New Member

Joined

Apr 24, 2017

Messages

4

• Apr 24, 2017

• #3

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

 

Upvote 0

G

gaz_chops

Well-known Member

Joined

Apr 29, 2003

Messages

6,485

Platform

1. MacOS

• Apr 24, 2017

• #4

Code:

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

 

Upvote 0

63falcondude

Well-known Member

Joined

Jan 15, 2016

Messages

3,572

• Apr 24, 2017

• #5

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

 

Upvote 0

M

mgskater12

New Member

Joined

Apr 24, 2017

Messages

4

• Apr 24, 2017

• #6

63falcondude said:

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

Click to expand...

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

 

Upvote 0

63falcondude

Well-known Member

Joined

Jan 15, 2016

Messages

3,572

• Apr 24, 2017

• #7

mgskater12 said:

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

Click to expand...

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

 

Upvote 0

63falcondude

Well-known Member

Joined

Jan 15, 2016

Messages

3,572

• Apr 24, 2017

• #8

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

 

Upvote 0

R

RicardoCubed

Board Regular

Joined

Jul 10, 2013

Messages

206

Office Version

1. 365

Platform

1. Windows

• Apr 24, 2017

• #9

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

 

Upvote 0

M

mgskater12

New Member

Joined

Apr 24, 2017

Messages

4

• Apr 24, 2017

• #10

63falcondude said:

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

Click to expand...

Works perfect!

Thanks!

 

Upvote 0