Apr 16 2022 04:16 PM
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!
Apr 18 2022 12:57 PM
=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".