Calculate sum ignoring filter

[vipett]

I have one table with a lot of transactions from different sales companies to different customers and I would like to select only a few customers and display a matrix, showing how much each customer has contributed to each sales company, in relation to the total sales for that sales company.

For example if sales company X has sold for 100 USD one month, and Customer A has bought for 50 USD that month, I want 50 to show up in that matrix.

I can manage it with all customers, but as soon as I start filtering to only a few important customers, the % becomes incorrect..

 

[Toadstool]

Hi Vipett,

It would be best if you could supply anonymized examples of your data with desired results, preferably using XL2BB

I only have Excel 2016 available so your later version may have more options, but as a hint I'd look at SUBTOTAL and AGGREGATE to only include filtered results.