Getting a list and counts based on a value

New Contributor

Greetings. Using my screen shot as a reference, columns A-D have raw data. There are 12 sample rows, but it would be many thousands in reality.




Based on who I set the customer to (cell E2), I want columns F-H to be dynamically populated.


Any thoughts on a formula I can enter into cell F2 that might dynamically populate this? Of course, I can use a Filter on column A and copy/paste the result (and that's what I've been doing till now), but I have other plans that would make this way much more efficient for me.


Lest we fall victim to the X/Y problem, what I'm ultimately wanting is for columns J and F to get a total number of each product that a particular customer has purchased. If there's a way to skip doing the stuff in columns F-H, I'm fine with that too.

3 Replies
best response confirmed by theglossy1 (New Contributor)


With the new dynamic arrayformulas you can try this:


products and quantities of a customer.JPG



An alternative could be Power Query. In the attached file you can add data to the large blue dynamic table (range A1:D23 in the screenshot) and you can enter the customer in the small blue dynamic table which is in range E1:E2. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.

customer product quantity.JPG

Whoa, nice... thanks!