SumProduct Alternative - Performance Issues

[MTURKS83]

Hi,

I quite often use SumProduct to perform calculations where I have multiple criteria in rows and at least 1 criteria in the columns, the formula may look like:
=SUMPRODUCT((Column1 = X)*(Column 2 = Y)*(Row1 = A)* (Data Set))

The number of rows in Column1/2 could be 5000 plus and the Row1 would normally be about 50 columns across

I find that this will make the workbook extremely slow and was hoping that something could provide an alternative formula that is not so calculation intensive

Thanks!

 

[offthelip]

The way I do those sort of calculations is to use a "Helper" column, where you create an extra column with both "column" conditions in it, then you can use the IFS functions on the row. This will be much faster than sum product

 

[MTURKS83]

Thanks Offthelip. I'm not 100% sure how that would work when I am trying to retrieve a sum of values from a dataset based on both column and row criteria?

I'm thinking only SUMPRODUCT allows this calculation? Maybe the best idea is to reorganise the dataset so it is aggregated and a standard SUMIF can be performed?

Many thanks