Find unique values with multiple conditions

[frumpy]

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]

Try:


<tr
></tr

<tr
></tr

	A	B	C	D	E	F	G	H	I
1	Company	Investment Type	Investment Date	Purchase Amount	Location
2	Company 1	Normal	6/1/2017	$100,000	San Francisco, CA		Special Investments	5
Company 2	Normal	7/1/2017	$100,000	San Francisco, CA		Special Companies (Unique)		3
4	Company 3	Normal	7/1/2017	$100,000	San Francisco, CA
5	Company 4	Normal	7/1/2017	$100,000	San Francisco, CA		Unique Companies	24
6	Company 5	Normal	7/1/2017	$100,000	San Francisco, CA		Unique Companies in SF		17
7	Company 6	Normal	8/1/2017	$100,000	San Francisco, CA
8	Company 7	Normal	9/1/2017	$100,000	San Francisco, CA
9	Company 8	Normal	9/1/2017	$100,000	San Francisco, CA
10	Company 9	Normal	10/1/2017	$100,000	San Francisco, CA
11	Company 10	Normal	10/1/2017	$100,000	San Francisco, CA
12	Company 11	Normal	10/1/2017	$200,000	San Francisco, CA
13	Company 12	Normal	10/1/2017	$100,000	San Francisco, CA
14	Company 13	Special	10/1/2017	$120,000	San Francisco, CA
15	Company 13	Special	3/1/2018	$200,000	San Francisco, CA
16	Company 14	Normal	11/1/2017	$100,000	San Francisco, CA
17	Company 15	Normal	11/1/2017	$100,000	San Francisco, CA
18	Company 16	Normal	11/1/2017	$50,000	San Francisco, CA
19	Company 17	Special	11/1/2017	$100,000	San Francisco, CA
20	Company 18	Special	11/1/2017	$100,000	New York, NY
21	Company 18	Special	12/1/2017	$100,000	New York, NY
22	Company 19	Normal		$100,000	New York, NY
23	Company 20	Normal		$100,000	New York, NY
24	Company 21	Special		$100,000	New York, NY
25	Company 22	Normal	4/1/2018	$100,000	New York, NY
26	Company 23	Normal	4/1/2018	$100,000	New York, NY
27	Company 24	Normal	4/1/2018	$100,000	New York, NY

<tbody>
</tbody>

Sheet4​

Array Formulas Cell Formula 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.

 

[Aladin Akyurek]

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]

thank you! works well!

 

[Eric W]

Glad we could help!

 

[Aladin Akyurek]

Ditto...