I need help to create a formula!

New Contributor

Under the column "Property" how many Bandon and then that answer would show in the table at the end.

How many Boca? and so on. 

Last NameProperty
RamirezBandon
BensonBoca
ElstonBoca
GunzelBrookside
PerezBrookside
DuranMetroplex
DeMossMira Vista
SantiagoRed River
ThangRed River
SellWestminster
BlackWestminster
  
  
Bandon1
Boca2
Brookside2
Metroplex1
Red River2
Mira Vista1
Westminster2
Wood Creek0
6 Replies

@Cheryl0818 

You can use COUNTIF formulas to count the frequencies:

In B15:

=COUNTIF($B$2:$B$12,A15)

Fill down to B22.

@Cheryl0818 

As variant for Excel 365

=LET(
  p, UNIQUE( property ),
  c, SCAN(0, p, LAMBDA(a,v, SUM( --(property=v) )) ),
  IF( {1,0}, p, c ) )

@Hans Vogelaar   Thank you so much!!!

@Sergei Baklan 

Please note that the table at the bottom contains a property that does not occur at the top, hence the count of 0.

So we cannot use UNIQUE or a pivottable to create the table at the bottom.

@Hans Vogelaar 

Oops... Thank you, I missed that. When simply

=SCAN(0, propertyToCount, LAMBDA(a,v, SUM( --(property=v) )) )
Thanks - I appreciate this!