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 sh...
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.
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