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>[TABLE="width: 0"]
<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>[TR]
[TD]Unique Identifier[/TD]
[TD]Engagement[/TD]
[TD]Eng Group[/TD]
[TD]Policy[/TD]
[TD]Elected[/TD]
[TD]Collective[/TD]
[TD]Advocacy[/TD]
[TD]Alumni Region[/TD]
[TD]Advocacy Interest 2018[/TD]
[TD]Ad Interest Consolidated[/TD]
[TD]Advocacy Commitment[/TD]
[TD]ADVOCACY Commit Consolidated[/TD]
[TD]Policy Interest 2018[/TD]
[TD]Policy Interest Consolidated[/TD]
[TD]Policy Commitment[/TD]
[TD]Policy Commit Consolidated[/TD]
[TD]Appointed Interest 2018[/TD]
[TD]PLA YN[/TD]
[TD]LPLF YN[/TD]
[TD]RPLS YN[/TD]
[TD]DPLS YN[/TD]
[TD]PASF YN[/TD]
[TD]NPAW YN[/TD]
[/TR]
[TR]
[TD]0034000000y3SMCAA2[/TD]
[TD]4[/TD]
[TD]2-5s[/TD]
[TD]D[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]A[/TD]
[TD]National Capital Region[/TD]
[TD="align: right"]2[/TD]
[TD]Yes[/TD]
[TD="align: right"]2[/TD]
[TD]Low[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]0034000000y3SgkAAE[/TD]
[TD]2b[/TD]
[TD]2-5s[/TD]
[TD]D[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]A[/TD]
[TD]South Dakota[/TD]
[TD="align: right"]2[/TD]
[TD]Yes[/TD]
[TD="align: right"]2[/TD]
[TD]Low[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]0034000000zgeMPAAY[/TD]
[TD]4[/TD]
[TD]2-5s[/TD]
[TD]C1[/TD]
[TD]C2[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]Illinois & Northwest Indiana[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]I'm not sure[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]0034000000y3SZwAAM[/TD]
[TD]3[/TD]
[TD]2-5s[/TD]
[TD]A[/TD]
[TD]D[/TD]
[TD]C[/TD]
[TD]A[/TD]
[TD]Los Angeles[/TD]
[TD="align: right"]2[/TD]
[TD]Yes[/TD]
[TD="align: right"]1[/TD]
[TD]Low[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]I'm not sure[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]0034000000y3QASAA2[/TD]
[TD]4[/TD]
[TD]2-5s[/TD]
[TD]E[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]C1[/TD]
[TD]National Capital Region[/TD]
[TD="align: right"]2[/TD]
[TD]Yes[/TD]
[TD="align: right"]1[/TD]
[TD]Low[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]0034000000y3SjuAAE[/TD]
[TD]4[/TD]
[TD]2-5s[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]E[/TD]
[TD]Colorado[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]00340000015yEdbAAE[/TD]
[TD]5[/TD]
[TD]2-5s[/TD]
[TD]B[/TD]
[TD]C1[/TD]
[TD]D[/TD]
[TD]C2[/TD]
[TD]National Capital Region[/TD]
[TD="align: right"]2[/TD]
[TD]Yes[/TD]
[TD="align: right"]2[/TD]
[TD]Low[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]0034000001j1p9LAAQ[/TD]
[TD]4[/TD]
[TD]2-5s[/TD]
[TD]B[/TD]
[TD]C1[/TD]
[TD]B[/TD]
[TD]C1[/TD]
[TD]Sacramento[/TD]
[TD="align: right"]3[/TD]
[TD]Yes[/TD]
[TD="align: right"]3[/TD]
[TD]Medium[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[/TR]
</tbody>[/TABLE]

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.