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!

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 920"]
<tbody>[TR]
[TD]Company[/TD]
[TD]Investment Type[/TD]
[TD]Investment Date[/TD]
[TD]Purchase Amount[/TD]
[TD]Location[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Company 1[/TD]
[TD]Normal[/TD]
[TD="align: right"]6/1/17[/TD]
[TD="align: right"]$100,000[/TD]
[TD]San Francisco, CA[/TD]
[TD][/TD]
[TD]Special Investments[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Company 2[/TD]
[TD]Normal[/TD]
[TD="align: right"]7/1/17[/TD]
[TD="align: right"]$100,000[/TD]
[TD]San Francisco, CA[/TD]
[TD][/TD]
[TD]Special Companies (Unique)[/TD]
[TD][/TD]
[TD]should be 3[/TD]
[/TR]
[TR]
[TD]Company 3[/TD]
[TD]Normal[/TD]
[TD="align: right"]7/1/17[/TD]
[TD="align: right"]$100,000[/TD]
[TD]San Francisco, CA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Company 4[/TD]
[TD]Normal[/TD]
[TD="align: right"]7/1/17[/TD]
[TD="align: right"]$100,000[/TD]
[TD]San Francisco, CA[/TD]
[TD][/TD]
[TD]Unique Companies[/TD]
[TD="align: right"]24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Company 5[/TD]
[TD]Normal[/TD]
[TD="align: right"]7/1/17[/TD]
[TD="align: right"]$100,000[/TD]
[TD]San Francisco, CA[/TD]
[TD][/TD]
[TD]Unique Companies in SF[/TD]
[TD][/TD]
[TD]should be 17[/TD]
[/TR]
[TR]
[TD]Company 6[/TD]
[TD]Normal[/TD]
[TD="align: right"]8/1/17[/TD]
[TD="align: right"]$100,000[/TD]
[TD]San Francisco, CA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Company 7[/TD]
[TD]Normal[/TD]
[TD="align: right"]9/1/17[/TD]
[TD="align: right"]$100,000[/TD]
[TD]San Francisco, CA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Company 8[/TD]
[TD]Normal[/TD]
[TD="align: right"]9/1/17[/TD]
[TD="align: right"]$100,000[/TD]
[TD]San Francisco, CA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Company 9[/TD]
[TD]Normal[/TD]
[TD="align: right"]10/1/17[/TD]
[TD="align: right"]$100,000[/TD]
[TD]San Francisco, CA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Company 10[/TD]
[TD]Normal[/TD]
[TD="align: right"]10/1/17[/TD]
[TD="align: right"]$100,000[/TD]
[TD]San Francisco, CA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Company 11[/TD]
[TD]Normal[/TD]
[TD="align: right"]10/1/17[/TD]
[TD="align: right"]$200,000[/TD]
[TD]San Francisco, CA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Company 12[/TD]
[TD]Normal[/TD]
[TD="align: right"]10/1/17[/TD]
[TD="align: right"]$100,000[/TD]
[TD]San Francisco, CA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Company 13[/TD]
[TD]Special[/TD]
[TD="align: right"]10/1/17[/TD]
[TD="align: right"]$120,000[/TD]
[TD]San Francisco, CA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Company 13[/TD]
[TD]Special[/TD]
[TD="align: right"]3/1/18[/TD]
[TD="align: right"]$200,000[/TD]
[TD]San Francisco, CA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Company 14[/TD]
[TD]Normal[/TD]
[TD="align: right"]11/1/17[/TD]
[TD="align: right"]$100,000[/TD]
[TD]San Francisco, CA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Company 15[/TD]
[TD]Normal[/TD]
[TD="align: right"]11/1/17[/TD]
[TD="align: right"]$100,000[/TD]
[TD]San Francisco, CA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Company 16[/TD]
[TD]Normal[/TD]
[TD="align: right"]11/1/17[/TD]
[TD="align: right"]$50,000[/TD]
[TD]San Francisco, CA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Company 17[/TD]
[TD]Special[/TD]
[TD="align: right"]11/1/17[/TD]
[TD="align: right"]$100,000[/TD]
[TD]San Francisco, CA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Company 18[/TD]
[TD]Special[/TD]
[TD="align: right"]11/1/17[/TD]
[TD="align: right"]$100,000[/TD]
[TD]New York, NY[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Company 18[/TD]
[TD]Special[/TD]
[TD="align: right"]12/1/17[/TD]
[TD="align: right"]$100,000[/TD]
[TD]New York, NY[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Company 19[/TD]
[TD]Normal[/TD]
[TD][/TD]
[TD="align: right"]$100,000[/TD]
[TD]New York, NY[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Company 20[/TD]
[TD]Normal[/TD]
[TD][/TD]
[TD="align: right"]$100,000[/TD]
[TD]New York, NY[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Company 21[/TD]
[TD]Special[/TD]
[TD][/TD]
[TD="align: right"]$100,000[/TD]
[TD]New York, NY[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Company 22[/TD]
[TD]Normal[/TD]
[TD="align: right"]4/1/18[/TD]
[TD="align: right"]$100,000[/TD]
[TD]New York, NY[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Company 23[/TD]
[TD]Normal[/TD]
[TD="align: right"]4/1/18[/TD]
[TD="align: right"]$100,000[/TD]
[TD]New York, NY[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Company 24[/TD]
[TD]Normal[/TD]
[TD="align: right"]4/1/18[/TD]
[TD="align: right"]$100,000[/TD]
[TD]New York, NY[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Try:


<tr
></tr

<tr
></tr

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

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]6/1/2017[/TD]
[TD="align: right"]$100,000[/TD]

[TD="align: right"][/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"]7/1/2017[/TD]
[TD="align: right"]$100,000[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]7/1/2017[/TD]
[TD="align: right"]$100,000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]7/1/2017[/TD]
[TD="align: right"]$100,000[/TD]

[TD="align: right"][/TD]

[TD="align: right"]24[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]7/1/2017[/TD]
[TD="align: right"]$100,000[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]17[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]8/1/2017[/TD]
[TD="align: right"]$100,000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]9/1/2017[/TD]
[TD="align: right"]$100,000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]9/1/2017[/TD]
[TD="align: right"]$100,000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]10/1/2017[/TD]
[TD="align: right"]$100,000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]10/1/2017[/TD]
[TD="align: right"]$100,000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]10/1/2017[/TD]
[TD="align: right"]$200,000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]10/1/2017[/TD]
[TD="align: right"]$100,000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]10/1/2017[/TD]
[TD="align: right"]$120,000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]3/1/2018[/TD]
[TD="align: right"]$200,000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]11/1/2017[/TD]
[TD="align: right"]$100,000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]11/1/2017[/TD]
[TD="align: right"]$100,000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]11/1/2017[/TD]
[TD="align: right"]$50,000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]11/1/2017[/TD]
[TD="align: right"]$100,000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]11/1/2017[/TD]
[TD="align: right"]$100,000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: right"]12/1/2017[/TD]
[TD="align: right"]$100,000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]

[TD="align: right"][/TD]
[TD="align: right"]$100,000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]

[TD="align: right"][/TD]
[TD="align: right"]$100,000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]

[TD="align: right"][/TD]
[TD="align: right"]$100,000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]

[TD="align: right"]4/1/2018[/TD]
[TD="align: right"]$100,000[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]

[TD="align: right"]4/1/2018[/TD]
[TD="align: right"]$100,000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]

[TD="align: right"]4/1/2018[/TD]
[TD="align: right"]$100,000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]I3[/TH]
[TD="align: left"]{=SUM(SIGN(FREQUENCY(IF(B2:B27="Special",IF(ISNUMBER(C2:C27),MATCH(A2:A27,A2:A27,0))),ROW(A2:A27)-ROW(A2)+1)))}[/TD]
[/TR]
[TR]
[TH]I6[/TH]
[TD="align: left"]{=SUM(SIGN(FREQUENCY(IF(E2:E27="San Francisco, CA",MATCH(A2:A27,A2:A27,0)),ROW(A2:A27)-ROW(A2)+1)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]


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