SUMIF with an area as criteria range

[Pasi84]

Unfortunately my longer message on this got lost in space after I had timed out from the forum. But here is a quick recap:

The situation
I have a database that has all the purchases made from us. On rows are individual deals, and on colums I have: Year, Buyer 1, Buyer 2, Buyer 3...Buyer 8, Value, Region, Category...

What I need to do, is a table that has on rows, different client names and on colums for example years or regions. In the cells I need to sum the total value client X has purchased in year Y or from region Z.

To do this, I entered a sumifs-formula with multiple further criteria (it can't be a specific category or value has to be larger than something...). This works fine when I do it for column "Buyer 1" but because I want it to sum up the deal values whenever Client X is in any of the colums from Buyer 1 to Buyer 8, it doesn't. My "short"-cut was to copy paste the sumif-formula multiple times so that there is one for each buyer column. Obviously this makes the sheet slow and difficult to run or update.

Therefore is there a way of doing this within one sumif-formula?

Unfortunately I cannot paste a sheet right now, because my excel is stuck (because of this issue) but I'll do it as soon as possible if needed.

Thank you for any help you can provide!

[zbor]

Looks like job for Pivot table... But example workbook would be best.
Make sure that you avoid important data but keep similiar input.

[Pasi84]

The problem with a pivot table is that the wanted table feeds data directly into a model. Preferably when something is updated in the initial criteria, the changes would follow through the model as well. I don't know if this is possible, because for example the number columns in the pivot table can decrease when years are removed etc.

I'll try to get you an excel file as soon as possible. I can't edit the original, but I'll do a quick replica.

[Pasi84]

Sorry about not being able to post the example; I've been buried in work.

I managed to find a solution by using sumproduct and find on the whole range of buyers C1:E2000. Unfortunately this formula is even slower even though much managable in terms of updating. I tried to speed it up by combining the buyers into a single string with concatenate, and although this sped up the calculation, it is still considerably slower than the sumifs*8-mess I am using now.

Therefore this leads me to conclude that a macro would be the only way of doing this effectively and efficiently. However that would remove any transparency from the work I do, and has to be ruled out on those grounds.