0

I wondered if there was a way of making the following formula less memory intensive. I am currently using a SUMIF with a contains criteria to see if a substring is contained in another cell. Performing this over a large amount of data, using the wildcard approach I have opted for, is causing the sheet to become incredibly slow.

I wondered if there was a more optimal solution to the following part that I have put in bold? (Or the rest of the formula too!).

Additionally, can this be put into an array formula with the rows and columns in B:B and 1:1 the constraints?

=SUMIFS('Sheet1'!$R$2:$R,'Sheet1'!$D$2:$D,"<="&$A3,'Sheet1'!$E$2:$E,">="&$A3, 'Sheet1'!$C$2:$C, RIGHT(B$2,len(B$2)-FIND("_", B$2)), 'Sheet1'!$O$2:$O, **"*"&LEFT(B$2,FIND("_", B$2)-1)&"*"**)
MaartenDev
  • 5,631
  • 5
  • 21
  • 33
ewldh20
  • 93
  • 10
  • Is this something to be done on a regular basis, or as a one-off to clean up some existing data. – Tedinoz Jan 27 '20 at 10:50
  • I just noticed an earlier question [Google Sheets ArrayFormula with Sumifs](https://stackoverflow.com/q/30081491/1330560). Would you please outline the research and trouble shooting that you did before you posted your question. – Tedinoz Jan 27 '20 at 10:54
  • Hi there @ewldh20! Could you share a replica of your sheet? That way we all could focus better on the optimization path. Also, you can read some optimization techniques on @Tedinoz link. – Jacques-Guzel Heron Jan 27 '20 at 11:00
  • Hi all, thanks for this. How I got around it was to use a filter function and basically a sumif for each category to see if the field had been used. Therefore, it brought the range down from over 100 possible outcomes to the 15 or so that were used! – ewldh20 Jan 28 '20 at 14:08

0 Answers0