Please help Adding a count of unique values to a current calc

[debralee]

Good morning,

The below calc works as it should. I now need to add to this calc using the below as a criteria and counting total unique values from column $B:$B if they meet the below criteria.

=COUNTIFS(SURVEYS!$N:$N,"=Jacksonville",SURVEYS!$A:$A,"=ANSWERED")

Please help

 

[Eric W]

Try:

=SUM(SIGN(FREQUENCY(IF(Surveys!A1:A50="Answered",IF(Surveys!N1:N50="Jacksonville",MATCH(Surveys!B1:B50,Surveys!B1:B50,0))),ROW(A1:A50)-ROW(A1)+1)))

confirmed with Control+Shift+Enter.

 

[debralee]

Greatly appreciate the help but it does not seem to work. Also, If I change Jacksonville to something else, I get a value error.

Any thoughts on that?

 

[Eric W]

When you say "does not seem to work", what do you mean? Wrong answer, error message? If you get the #VALUE ! error, it could be that you are not entering the formula as an array. Select the cell where you want it, past the formula in the formula bar, change the references to match your sheet, then hold down the Control and Shift keys, then press Enter.

Here's what I get:

	A	B	C	N	O	P	Q	R
1		a		Jacksonville		Unique count		Counif
2		b		Jacksonville		3		4
3		c		Jacksonville
4		d		Other
5	ANSWERED	e		Other
6		f		Other
7		a		Jacksonville
8	ANSWERED	b		Jacksonville		x
9	ANSWERED	c		Jacksonville		x
10	ANSWERED	d		Jacksonville		x
11		e		Jacksonville
12		f		Other
13		a		Jacksonville
14	ANSWERED	b		Jacksonville		x - duplicate
15		c		Jacksonville
16		d		Jacksonville
17		e		Other
18		f		Other
19		a		Jacksonville
20		b		Jacksonville
21		c		Jacksonville
22	ANSWERED	d		Other
23		e		Other
24		f		Jacksonville
25		a		Jacksonville
26	ANSWERED	b		Other
27		c		Jacksonville
28		d		Jacksonville
29		e		Jacksonville
30		f		Other

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Surveys​

Worksheet Formulas Cell Formula R2 =COUNTIFS(Surveys!$N:$N,"=Jacksonville",Surveys!$A:$A,"=ANSWERED") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula P2 {=SUM(SIGN(FREQUENCY(IF(Surveys!A1:A50="Answered",IF(Surveys!N1:N50="Jacksonville",MATCH(Surveys!B1:B50,Surveys!B1:B50,0))),ROW(A1:A50)-ROW(A1)+1)))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

If you make changes to the formula, like changing the city, you need to re-enter the Control+Shift+Enter. Given that, it may be easier to put the city in a work cell of its own, like Q1:

=SUM(SIGN(FREQUENCY(IF(Surveys!A1:A50="Answered",IF(Surveys!N1:N50=Q1,MATCH(Surveys!B1:B50,Surveys!B1:B50,0))),ROW(A1:A50)-ROW(A1)+1)))

with CSE.