Forum Discussion

DazExcel's avatar
DazExcel
Copper Contributor
Oct 07, 2023

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...
BI & Data Analysis
excel
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Oct 07, 2023

SergeiBaklan 

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)
  )

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Oct 07, 2023

PeterBartholomew1 

Oh, yes, I was between breakfast and lunch. Plus you spent more time to generate friendly source table.

Resources