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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

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,280
Messages
5,571,289
Members
412,376
Latest member
osborne
Top