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

reaktorblue

Board Regular
Joined
Aug 8, 2007
Messages
78
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]<>"")))
 

Some videos you may like

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
Joined
Jun 12, 2014
Messages
50,653
Office Version
  1. 365
Platform
  1. Windows
How about
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
Joined
Aug 8, 2007
Messages
78
How about
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
Joined
Jun 12, 2014
Messages
50,653
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,294
Messages
5,571,375
Members
412,385
Latest member
OChambo94
Top