The attachment contains a data table from which I am trying to get all the information according to multiple criteria.
In the real model the data table contains only up to 400 rows, but there are nine fruits, seven types and nineteen countries, making 285 combinations (types only apply to apples).
As each year is a separate field I can't find a way to make it amenable to a pivot table.
I can handle the multiple criteria, but am having difficulty finding the best way to make multiple simultaneous queries.
The way most obvious to me is to use array formula of the kind "=sum((country="France")*(fruit="Apple")*(Type="Pippin")*1997)". This works fine, but it is quite slow in recalculating, and not favoured by some(!)
My example uses multiple DSUMs, which works well in the example, though it's not very elegant. I'm flummoxed by the need to include a set of column labels with every individual query.
Is there a better way, please?
tia