Forum Discussion

rshenk3's avatar
rshenk3
Copper Contributor
Mar 30, 2023

Summing Split Filtered Values

Is it possible to SUM a list of SPLIT values that were compiled using the FILTER function, all in one formula?

excel
Formulas and Functions
  • mathetes's avatar
    mathetes
    Silver Contributor
    Mar 30, 2023

    rshenk3 

     

    Normally you can simply write =SUM(FILTER(range, criteria...)) 

     

    But maybe you've already tried that. Are you saying that the contents of rows 17 through 24 of your image are what you want to sum? You write SPLIT as if it itself is a function; it's not, at least not included in any resources I'm seeing.

    • rshenk3's avatar
      rshenk3
      Copper Contributor
      Mar 30, 2023

      Thank you for your reply!
      This is the formula I tried using
      =SUM(SPLIT((FILTER('Order Import'!X:X,'Order Import'!AD:AD=A14),",",FALSE)))

      but it only returns the sum of the first cell in the filter. I would like it to return the sum of all the values returned by the FILTER function.

      • Harun24HR's avatar
        Harun24HR
        Bronze Contributor
        Mar 30, 2023

        rshenk3 First clarify, are you Excel or google-sheet? SPLIT() is function of google-sheet, not excel. To split multiple values at a time you need to array formula. So, try-

        =SUM(ArrayFormula(SPLIT((FILTER('Order Import'!X:X,'Order Import'!AD:AD=A14),",",FALSE))))

Resources