How to dynamically total/count across multiple columns of a dynamic array

SergeiBaklan 

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.

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