Datasets are too large for use - is there a smarter way to sumproduct?

[Mark McInerney]

Hi,

I have a large dataset. I need to use the sumproduct function to retrieve data from the dataset - namely if certain criteria are met on several columns on the x axis, and several criteria are met on the Y axis it will retrieve that value:

=SUMPRODUCT(('Daily Source'!$C$3:$ZZ$3='Daily Reformat'!$B$2)*('Daily Source'!$C$6:$ZZ$6='Daily Reformat'!$C$2)*('Daily Source'!$C$4:$ZZ$4='Daily Reformat'!$C$3)*('Daily Source'!$B$7:$B$2000='Daily Reformat'!B4),'Daily Source'!$C$7:$ZZ$2000)

It is taking forever for the formulas to run - is there a quicker, smarter way to achieve the same outcome? Could Index Match be used?

I need to check potentially 3 criteria on both axis and then return the value when all criteria are met.

Thanks - Mark.

 

[RoryA]

If there’s only one column matching the first three criteria, I’d suggest putting that into a separate formula and referring to it in Index/match. A pivot table might also work; hard to be sure from what you’ve posted.