countifs multiple criteria

[akswartz85]

I'm trying to convert a countifs that currently counts non-unique values into one that counts unique values.

The non-unique formula: =COUNTIFS('FULL RAW'!$H:$H,'Summary - Non Unique Counts'!$A6,'FULL RAW'!$D:$D,"B",'FULL RAW'!$T:$T,"Yes",'FULL RAW'!$P:$P,"High")

How do I convert that into one that returns a unique count?

Thanks!

 

[Aladin Akyurek]

Which range do you need to run a unique count on?

 

[akswartz85]

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

Unique Identifier

Engagement

Eng Group

Policy

Elected

Collective

Advocacy

Alumni Region

Advocacy Interest 2018

Ad Interest Consolidated

Advocacy Commitment

ADVOCACY Commit Consolidated

Policy Interest 2018

Policy Interest Consolidated

Policy Commitment

Policy Commit Consolidated

Appointed Interest 2018

PLA YN

LPLF YN

RPLS YN

DPLS YN

PASF YN

NPAW YN

0034000000y3SMCAA2

4

2-5s

D

B

B

A

National Capital Region

2

Yes

2

Low

4

0

Yes

Yes

No

No

No

No

No

0034000000y3SgkAAE

2b

2-5s

D

B

B

A

South Dakota

2

Yes

2

Low

0

0

No

No

No

No

No

No

No

0034000000zgeMPAAY

4

2-5s

C1

C2

C

D

Illinois & Northwest Indiana

1

0

1

0

I'm not sure

No

Yes

No

No

Yes

No

0034000000y3SZwAAM

3

2-5s

A

D

C

A

Los Angeles

2

Yes

1

Low

1

0

I'm not sure

No

No

No

No

No

No

0034000000y3QASAA2

4

2-5s

E

B

B

C1

National Capital Region

2

Yes

1

Low

0

0

Yes

No

No

No

No

No

No

0034000000y3SjuAAE

4

2-5s

A

B

B

E

Colorado

1

0

1

0

No

No

No

No

No

No

No

00340000015yEdbAAE

5

2-5s

B

C1

D

C2

National Capital Region

2

Yes

2

Low

4

0

Yes

No

No

No

No

No

No

0034000001j1p9LAAQ

4

2-5s

B

C1

B

C1

Sacramento

3

Yes

3

Medium

0

0

Yes

No

No

Yes

No

Yes

Yes

<colgroup><col style="width: 209px"><col width="102"><col width="102"><col width="51"><col width="63"><col width="80"><col width="77"><col width="300"><col width="206"><col width="206"><col width="180"><col width="180"><col width="179"><col width="179"><col width="155"><col width="155"><col width="629"><col width="94"><col width="94"><col width="94"><col width="94"><col width="94"><col width="94"></colgroup><tbody>
</tbody>

Here's a cut of the data. I'm counting col A (unique ID) - essentially, how many unique IDs fit the above criteria?

 

[akswartz85]

I've tried this:
=ArrayFormula(sum(--(frequency(if('FULL RAW'!$H:$H='Summary - Non Unique Counts'!$A6)*('FULL RAW'!$D:$D="B")*('FULL RAW'!$T:$T="Yes")*('FULL RAW'!$P:$P="High"),'FULL RAW'!$A:$A),0)))

also this:
=ArrayFormula(((sum(--(frequency(if('FULL RAW'!$H:$H = 'Summary - Non Unique Counts'!$A6)*('FULL RAW'!$D:$D="B")*('FULL RAW'!$T:$T="Yes")*('FULL RAW'!$P:$P="High"),'FULL RAW'!$A:$A),'FULL RAW'!$A:$A),'FULL RAW'!$A:$A),'FULL RAW'!$A:$A)>0))

Errors on both, and I'm not sure why.

(I'm in Google Sheets, hence =arrayformula instead of the traditional {})

 

[Aladin Akyurek]

Control+shift+enter, not just enter (I leave out the sheet name for convenience) in Summary - Non Unique Counts:

=SUM(IF(FREQUENCY(IF($A$2:$A$400<>"",IF(($H$2:$H$400=$A6)*($D$2:$D$400="B")*($T$2:$T$400="Yes")*($P$2:$P$400="high"),MATCH($A$2:$A$400,$A$2:$A$400,0))),ROW($A$2:$A$400)-ROW(INDEX($A$2:$A$400,1,1))+1),1))

 

[akswartz85]

BRILLIANT! That worked. Thank you very much.

 

[Aladin Akyurek]

BRILLIANT! That worked. Thank you very much.

You are welcome.