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.