Forum Discussion
How to dynamically total/count across multiple columns of a dynamic array
Hi,
I'm trying to create a dynamic column that will give me a count based on two columns of a dynamic array.
So if I've got three products across four offices like this...
I want to show the unique Products by Office, which I can do like this...
I want to add a count of products and a sum of sales for each product by office - I can do as a regular, non-dynamic formula using COUNTIFS() and SUMIFS() but I'd like to have dynamic formulas so I can build the table into a LET function and have a fully dynamic table (the real data will be in a table so the dynamic summary table should change size depending on the data.
I completely accept that the Pivot Table will give the same result. One has a choice.
It is also faster to achieve the result provided:-
1. The developer has spent time to understand pivot tables
2. They have not already developed the worksheet formula and saved it as a Lambda function.
But then the same could be said for VBA or Python (give or take a few Command buttons, Refresh or menu picks). I suspect that to have fluency in each of these areas such as you do is rare. At present, I stick to the worksheet formula environment until the case to shift technologies is overwhelming.
I mentioned PivotTable only as comment to remind other options exist. For such case it doesn't require DAX and advanced data modelling skills. Refresh is a minus, more dynamic formatting is a plus.
Power Query here as easy as PivotTable, just two steps - query the source and GroupBy office/product. But it less common than PivotTable and also requires Refresh.
You have been busy while I was having breakfast!
My formula was
= LET( distinctCategories, SORT(UNIQUE(SalesTbl[[Office]:[Produce]]), {1,2}), office, CHOOSECOLS(distinctCategories, 1), product, CHOOSECOLS(distinctCategories, 2), entries, COUNTIFS(SalesTbl[Office], office, SalesTbl[Produce], product), sales, SUMIFS(SalesTbl[Sales], SalesTbl[Office], office, SalesTbl[Produce], product), HSTACK(office, product, entries, sales) )Oh, yes, I was between breakfast and lunch. Plus you spent more time to generate friendly source table.
For such sample
that could be
=LET( office, CHOOSECOLS(data, 1), product,CHOOSECOLS(data, 2), sales, CHOOSECOLS(data, 3), k, SEQUENCE(, ROWS(office), , 0), uOffice,UNIQUE(office), uOP, SORT(UNIQUE(CHOOSECOLS(data, {1,2}))), mm, --(office & product = TRANSPOSE(CHOOSECOLS(uOP, 1) & CHOOSECOLS(uOP, 2))), count, TRANSPOSE(MMULT(k, mm)), total, TRANSPOSE(MMULT(k, mm * sales)), VSTACK( {"Office","Product","Count","Total"}, HSTACK(uOP, count, total) ) )I will take a further look at this later, but my first impressions are that the FILTER achieves little and that SUMIFS can be part of a dynamic formula provided the criteria values are arrays and the references use structured references from the Tables and not hard-wired direct cell references.
It is also possible to use array shaping functions as an alternative to SUMIFS but you would lose the performance of SUMIFS in terms of its speed.
- Thank for the reply Peter,
I agree - I guess the biggest issue is getting the unique list by Product and Office, if they were individual columns the SUMIFS/COUNTIFS would be simple.This version of the workbook has a couple more formulas thrown in just for the hell of it! They calculate crosstabs and grand totals. All core Pivot Table concepts and it is the PT that would provide flexibility if that were needed. Here it is simple coded formulas
