Find unique values with multiple conditions

frumpy

New Member
Joined
Jun 9, 2015
Messages
4
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!

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

<tbody>
</tbody>

<tbody>
</tbody>
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,122
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:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
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))
 

Forum statistics

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

Some videos you may like

This Week's Hot Topics

Top