New Contributor

# I need help to create a formula!

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
6 Replies

# Re: I need help to create a formula!

You can use COUNTIF formulas to count the frequencies:

In B15:

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

Fill down to B22.

# Re: I need help to create a formula!

As variant for Excel 365

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

# Re: I need help to create a formula!

@Hans Vogelaar   Thank you so much!!!

# Re: I need help to create a formula!

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.

# Re: I need help to create a formula!

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

``=SCAN(0, propertyToCount, LAMBDA(a,v, SUM( --(property=v) )) )``

# Re: I need help to create a formula!

Thanks - I appreciate this!