Forum Discussion

BA_Max's avatar
BA_Max
Iron Contributor
May 24, 2024

Figuring out Excel Crash reason

Hi

 

I've posted a similar thing on the answers.microsoft but realised I couldn't upload a spreadsheet example.

 

My issue is that a specific formula seems to be causing a crash that didn't prior to the most recent update:

 

 

=SUMPRODUCT(BYROW(B4:B8,LAMBDA(unique_list,FILTER(F4:F21,G4:G21=unique_list))),C4:C8)

 

 

Pasting the above formula into a cell always results in a crash first time.

 

I've tried splitting this up into SUMPRODUCT and the LAMBDA portion, which seems to result in a crash maybe 50% of the time (or more, unsure what is triggering it).

 

The attached sheet is set up with the broken formula in cell B2 and the crash can be triggered by editing B2 Formula then hitting enter but this doens't always crash.

 

I've found sometimes if you swap the lookup letter area (i.e., change a G to a Z) then it can crash too, but unsure if there's a correlation between the letters and crashing (probably not).

 

Hope someone more versed in crashes can figure this one out! I'm assuming a future update will just have to patch this and I'll have to figure out an alternative formula for the meantime.

 

Please note that the error / crash does not occur on Excel for Web if you need to open the sheet and it won't open without crashing for you.

 

Thanks

 

M

 

  • dscheikey's avatar
    dscheikey
    Bronze Contributor

    BA_Max 

    Hi!

    I can offer you an alternative formula. I can't say what is causing your crash. I only use Excel for the web.

     

    =FILTER(F4:F21,ISNUMBER(XMATCH(G4:G21,B13:B17)))

     

    • BA_Max's avatar
      BA_Max
      Iron Contributor
      Thanks for the alternative; however, after an update (unsure which one), Excel has stopped crashing for this formula