Apr 21 2022 12:59 PM
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 Name | Property |
Ramirez | Bandon |
Benson | Boca |
Elston | Boca |
Gunzel | Brookside |
Perez | Brookside |
Duran | Metroplex |
DeMoss | Mira Vista |
Santiago | Red River |
Thang | Red River |
Sell | Westminster |
Black | Westminster |
Bandon | 1 |
Boca | 2 |
Brookside | 2 |
Metroplex | 1 |
Red River | 2 |
Mira Vista | 1 |
Westminster | 2 |
Wood Creek | 0 |
Apr 21 2022 01:30 PM - edited Apr 21 2022 01:30 PM
You can use COUNTIF formulas to count the frequencies:
In B15:
=COUNTIF($B$2:$B$12,A15)
Fill down to B22.
Apr 21 2022 01:46 PM
As variant for Excel 365
=LET(
p, UNIQUE( property ),
c, SCAN(0, p, LAMBDA(a,v, SUM( --(property=v) )) ),
IF( {1,0}, p, c ) )
Apr 21 2022 01:56 PM
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.
Apr 21 2022 02:57 PM
Oops... Thank you, I missed that. When simply
=SCAN(0, propertyToCount, LAMBDA(a,v, SUM( --(property=v) )) )