Excel Magic Trick 1193: Count Distinct Words As They Are Entered In Column: Formula or Excel Table?

See how calculate a running count for each unique (distinct) text items entered in column using two methods:
1. (00:10) Problem setup
2. (00:52) COUNTIFS with an Expandable Range, with the IF and ISBLANK functions
3. (02:56) Excel Table Feature, Table Formula Nomenclature (Structured References), COUNTIFS function and the INDEX function to create an Expandable Range

[excelisfun]

Excel Magic Trick 1193: Count Distinct Words As They Are Entered In Column: Formula or Excel Table?
See how calculate a running count for each unique (distinct) text items entered in column using two methods:
1. (00:10) Problem setup
2. (00:52) COUNTIFS with an Expandable Range, with the IF and ISBLANK functions
3. (02:56) Excel Table Feature, Table Formula Nomenclature (Structured References), COUNTIFS function and the INDEX function to create an Expandable Range

[barefacedquestions]

Now I understand why I often have problem with formulas in Excel table. Thank you, Mike.

[pmsocho]

Expandable range with structural references and INDEX - very cool!

[Sal_A]

Could you have used offset instead of index? Would it work?

[saito]

Hi, I'm working with this formula =TEXT(D4, "MMMM") to print the name of the month when a date is entered in a different column, it works fine however when I fill out all the spaces it populates January as default even tough no date has been entered.
There is formula to make those unused spaces blank until someone inputs on the trigger column?

[dilipkumarm]

what abt pivot? u can use count function there easily