Excel Spreadsheet: Slow Calculation Due to Array Table - Suggestions to Speed Up

Occasional Visitor

Hello,

 

I have an excel spreadsheet where I have to sum 4 different item combinations while looking up data from 3 different tabs for multiple columns:

 

=IFERROR(SUM(SUMIF('Demand Data'!$A$3:$H$200000,CONCATENATE('BLANK '!$B$5,"-",'BLANK '!$A$7,22020),'Demand Data'!$H$3:$H$200000),SUMIF('Demand Data'!$A$3:$H$200000,CONCATENATE('BLANK '!$B$4,"-",'BLANK '!$A$7,22020),'Demand Data'!$H$3:$H$200000),SUMIF('Demand Data'!$A$3:$H$200000,CONCATENATE('BLANK '!$B$3,"-",'BLANK '!$A$7,22020),'Demand Data'!$H$3:$H$200000),SUMIF('Demand Data'!$A$3:$H$200000,CONCATENATE('BLANK '!$B$2,"-",'BLANK '!$A$7,22020),'Demand Data'!$H$3:$H$200000)),"0")

 

Any suggestion on a better formula to speed up getting data to return faster?  Appreciate any help!

1 Reply

@vreichs0810 

=SUMPRODUCT((('Demand Data'!$A$6:$G$179=CONCATENATE('Blank '!$B$5,"-",'Blank '!$A$7,22020))+('Demand Data'!$A$6:$G$179=CONCATENATE('Blank '!$B$4,"-",'Blank '!$A$7,22020))+('Demand Data'!$A$6:$G$179=CONCATENATE('Blank '!$B$3,"-",'Blank '!$A$7,22020))+('Demand Data'!$A$6:$G$179=CONCATENATE('Blank '!$B$2,"-",'Blank '!$A$7,22020)))*'Demand Data'!$H$6:$H$179)

You can try this formula which recognizes entries in columns A to G in sheet "Demand Data".