Excel Magic Trick 1445: Single Cell Array Formula: Count Customer Names for 8 Sales Coupon Groups

See how to use Excel Formulas to analyze a group of customers who have been given coupons to three stores. Our goal is to create a single cell Array Formula that counts how many customers used coupons in one of eight coupon groups (all combinations of the three store coupons). See the Excel Spreadsheet Functions: SUM, MMULT, ROW, COUNTIFS and ISTEXT. Learn about how Array Formulas Work. Outline:
1) Each customer was given coupons to go to Wegmans, Publix, Trader Joe's.
2) We have a list of Stores names.
3) We have a list of transactions for the coupon redemptions that lists purchases, customer name and store
4) Goal 1: Count Customers who visited a given set of stores.
Topics in Video:
Related Videos:
Excel Magic Trick 1444: Logical Formulas: Count & Extract Customer Names for 8 Sales Coupon Groups
Excel Magic Trick 1444 Part 2: Count Customer Names for 8 Sales Coupon Groups
Excel Magic Trick 1445: Single Cell Array Formula: Count Customer Names for 8 Sales Coupon Groups
Excel Magic Trick 1446: Power Query Count & Extract Customer Names for 8 Sales Coupon Groups
Excel Magic Trick 1447: DAX Formulas & Data Model PivotTable to Count & List Stores Customers Visited

[Al-Ahdal]

Another great video Mike. It is requested to make videos on excel keyboard shortcuts, so we can able to use excel without mouse or toolbars.

[rockrick]

Great video! Can't wait for the Power Query and Dax version.

[Victor-ollo]

Absolutely amazing what is possible with array formulas !! Great video Mike, thanks a lot !!! Can´t wait to see the solutions for DAX and PQ.

[GaribaldiInTheMaking]

That countifs portion is amazing! Never saw that before. So using the entire MMULT the data extraction formula is:


"=INDEX($A$8:$A$19, AGGREGATE(15, 6, (ROW($A$8:$A$19)-ROW($A$7))/(MMULT(--((COUNTIFS($F$8:$F$27, $A$8:$A$19, $G$8:$G$27, $K$8:$M$8)>0)=ISTEXT($K8:$M8)), {1;1;1})=3), COLUMNS($N8:N8)))"

[vog]

Thanks for the video. I appreciate you still making videos containing some of the techniques of yesteryear, as a lot of companies unfortunately are still running older versions of Office. One thing that I haven't figured out yet, sometimes when I try F9 I'm unable to ctrl-z back to the formula, but I haven't figured out what causes this. Do you have any idea?

[wandersonnascimento]

Hi, I love your videos. I feel you could make a great video about shortcuts that allow us use excel without any mouse or too much access to toolbar

Thank you

[tomekgajowiak]

Mike. Please more more more Dax and Power Pivot