Forum Discussion
Getting a list and counts based on a value
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.
With the new dynamic arrayformulas you can try this:
=HSTACK(UNIQUE(FILTER(B2:B14,A2:A14=E2)),BYROW(UNIQUE(FILTER(B2:B14,A2:A14=E2)),LAMBDA(row,SUMIFS(D2:D14,A2:A14,E2,B2:B14,row))))
With the new dynamic arrayformulas you can try this:
=HSTACK(UNIQUE(FILTER(B2:B14,A2:A14=E2)),BYROW(UNIQUE(FILTER(B2:B14,A2:A14=E2)),LAMBDA(row,SUMIFS(D2:D14,A2:A14,E2,B2:B14,row))))- Whoa, nice... thanks!
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.
