# Find unique values with multiple conditions

#### frumpy

##### New Member
I've seen various q+a around this topic but can't get any of the suggested answers to work.

I need help crafting a formula to find the number of unique companies that are both Special per column B and have an Investment Date in column C. I'm able to calculate the total of 5 Special companies but can't figure out out to ignore the dupes. There are only 3 unique Special companies because Company 13 and 18 are duplicated. (Company 21 appropriately ignored as it lacks an Investment Date)

Similarly, I'd like to learn how many unique investments we have in SF. Companies should only be counted if they also have an Investment Date in column C and be ignored until they do.
How can I calculate the number of unique San Francisco companies with an Investment Date in column C? I don't want to double count Company 13+18.

Thanks!

 Company Investment Type Investment Date Purchase Amount Location Company 1 Normal 6/1/17 \$100,000 San Francisco, CA Special Investments 5 Company 2 Normal 7/1/17 \$100,000 San Francisco, CA Special Companies (Unique) should be 3 Company 3 Normal 7/1/17 \$100,000 San Francisco, CA Company 4 Normal 7/1/17 \$100,000 San Francisco, CA Unique Companies 24 Company 5 Normal 7/1/17 \$100,000 San Francisco, CA Unique Companies in SF should be 17 Company 6 Normal 8/1/17 \$100,000 San Francisco, CA Company 7 Normal 9/1/17 \$100,000 San Francisco, CA Company 8 Normal 9/1/17 \$100,000 San Francisco, CA Company 9 Normal 10/1/17 \$100,000 San Francisco, CA Company 10 Normal 10/1/17 \$100,000 San Francisco, CA Company 11 Normal 10/1/17 \$200,000 San Francisco, CA Company 12 Normal 10/1/17 \$100,000 San Francisco, CA Company 13 Special 10/1/17 \$120,000 San Francisco, CA Company 13 Special 3/1/18 \$200,000 San Francisco, CA Company 14 Normal 11/1/17 \$100,000 San Francisco, CA Company 15 Normal 11/1/17 \$100,000 San Francisco, CA Company 16 Normal 11/1/17 \$50,000 San Francisco, CA Company 17 Special 11/1/17 \$100,000 San Francisco, CA Company 18 Special 11/1/17 \$100,000 New York, NY Company 18 Special 12/1/17 \$100,000 New York, NY Company 19 Normal \$100,000 New York, NY Company 20 Normal \$100,000 New York, NY Company 21 Special \$100,000 New York, NY Company 22 Normal 4/1/18 \$100,000 New York, NY Company 23 Normal 4/1/18 \$100,000 New York, NY Company 24 Normal 4/1/18 \$100,000 New York, NY

<tbody>
</tbody>

<tbody>
</tbody>

#### Eric W

##### MrExcel MVP
Try:

<tr
></tr

<tr
></tr
ABCDEFGHI
1CompanyInvestment TypeInvestment DatePurchase AmountLocation
2Company 1Normal6/1/2017\$100,000San Francisco, CASpecial Investments5
Company 2Normal7/1/2017\$100,000San Francisco, CASpecial Companies (Unique)3
4Company 3Normal7/1/2017\$100,000San Francisco, CA
5Company 4Normal7/1/2017\$100,000San Francisco, CAUnique Companies24
6Company 5Normal7/1/2017\$100,000San Francisco, CAUnique Companies in SF17
7Company 6Normal8/1/2017\$100,000San Francisco, CA
8Company 7Normal9/1/2017\$100,000San Francisco, CA
9Company 8Normal9/1/2017\$100,000San Francisco, CA
10Company 9Normal10/1/2017\$100,000San Francisco, CA
11Company 10Normal10/1/2017\$100,000San Francisco, CA
12Company 11Normal10/1/2017\$200,000San Francisco, CA
13Company 12Normal10/1/2017\$100,000San Francisco, CA
14Company 13Special10/1/2017\$120,000San Francisco, CA
15Company 13Special3/1/2018\$200,000San Francisco, CA
16Company 14Normal11/1/2017\$100,000San Francisco, CA
17Company 15Normal11/1/2017\$100,000San Francisco, CA
18Company 16Normal11/1/2017\$50,000San Francisco, CA
19Company 17Special11/1/2017\$100,000San Francisco, CA
20Company 18Special11/1/2017\$100,000New York, NY
21Company 18Special12/1/2017\$100,000New York, NY
22Company 19Normal\$100,000New York, NY
23Company 20Normal\$100,000New York, NY
24Company 21Special\$100,000New York, NY
25Company 22Normal4/1/2018\$100,000New York, NY
26Company 23Normal4/1/2018\$100,000New York, NY
27Company 24Normal4/1/2018\$100,000New York, NY

<tbody>
</tbody>
Sheet4

Array Formulas
CellFormula
I3{=SUM(SIGN(FREQUENCY(IF(B2:B27="Special",IF(ISNUMBER(C2:C27),MATCH(A2:A27,A2:A27,0))),ROW(A2:A27)-ROW(A2)+1)))}
I6{=SUM(SIGN(FREQUENCY(IF(E2:E27="San Francisco, CA",MATCH(A2:A27,A2:A27,0)),ROW(A2:A27)-ROW(A2)+1)))}

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

(Sorry about the funky formatting in row 3.)

You can put the city in another cell to make it more flexible.

Last edited:

##### MrExcel MVP
Control+shift+enter, not just enter:

1. Special companies

=SUM(IF(FREQUENCY(IF(1-(\$A\$2:\$A\$27=""),IF(\$B\$2:\$B\$27="special",MATCH(\$A\$2:\$A\$27,\$A\$2:\$A\$27,0))),ROW(\$A\$2:\$A\$27)-ROW(\$A\$2)+1),1))

2. Special companies with investments

=SUM(IF(FREQUENCY(IF(1-(\$A\$2:\$A\$27=""),IF(\$B\$2:\$B\$27="special",IF(ISNUMBER(\$C\$2:\$C\$27),MATCH(\$A\$2:\$A\$27,\$A\$2:\$A\$27,0)))),ROW(\$A\$2:\$A\$27)-ROW(\$A\$2)+1),1))

3. Unique companies

=SUM(IF(FREQUENCY(IF(1-(\$A\$2:\$A\$27=""),MATCH(\$A\$2:\$A\$27,\$A\$2:\$A\$27,0)),ROW(\$A\$2:\$A\$27)-ROW(\$A\$2)+1),1))

4. San Francisco based companies

=SUM(IF(FREQUENCY(IF(1-(\$A\$2:\$A\$27=""),IF(ISNUMBER(SEARCH("san francisco",\$E\$2:\$E\$27)),MATCH(\$A\$2:\$A\$27,\$A\$2:\$A\$27,0))),ROW(\$A\$2:\$A\$27)-ROW(\$A\$2)+1),1))

#### frumpy

##### New Member
thank you! works well!

Ditto...

1,084,885
Messages
5,380,439
Members
401,679
Latest member
saffar