# Is there a formula that works similar to a pivot table's count filter that spills?

#### reaktorblue

##### Board Regular
Hello,

I'm using the following formula to create a list of unique entries, sorted alphabetically however, what I would like to do is also count how many times in my table those unique entries are mentioned. I've been doing this via pivot table however, if possible, I would like to do this via formula to avoid having to refresh the pivot table every time I would like to see it updated.

If I use "COUNTA" or "COUNTIF" formulas, although they work, they don't spill down like the below formula does. Is it possible to create a formula that spills down, giving a count on those unique entries?

VBA Code:
``=SORTBY(UNIQUE(FILTER(tblTransactions[Transaction Type],tblTransactions[Transaction Type]<>"")),UNIQUE(FILTER(tblTransactions[Transaction Type],tblTransactions[Transaction Type]<>"")))``

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

#### Fluff

##### MrExcel MVP, Moderator
Excel Formula:
``=COUNTIF(tblTransactions[Transaction Type],A2#)``
Change the A2 to the cell with your sortby formula.
You could possibly simplify your formula like
Excel Formula:
``=SORT(UNIQUE(FILTER(tblTransactions[Transaction Type],tblTransactions[Transaction Type]<>"")))``

#### reaktorblue

##### Board Regular
Excel Formula:
``=COUNTIF(tblTransactions[Transaction Type],A2#)``
Change the A2 to the cell with your sortby formula.
You could possibly simplify your formula like
Excel Formula:
``=SORT(UNIQUE(FILTER(tblTransactions[Transaction Type],tblTransactions[Transaction Type]<>"")))``

This works perfectly!! Thank you so much!

Also, I appreciate you taking the time to help me find a better way to write my original sort formula. Thanks a ton, that was awesome!

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
1
Views
103
Replies
1
Views
76
Replies
12
Views
238
Replies
3
Views
307
Replies
1
Views
201