Forum Discussion

Frederic_J's avatar
Frederic_J
Copper Contributor
May 24, 2024

SUMPRODUCT with * wildcard

Hello,   I would like to use the function sumproduct for a table dealing with different projects and their financial parameters, as well as their geography (country and region). I need to synthetiz...
excel
Formulas and Functions
Frederic_J's avatar
Frederic_J
Copper Contributor
May 24, 2024
Hi Martin, thank you for your answer. No, a simple average is not adapted unfortunately, I need to use a weighted average for the calculation of margin (weighted by the turnover actually).
  • Martin_Angosto's avatar
    Martin_Angosto
    Iron Contributor
    May 24, 2024

    Frederic_J 

     

    Hi, sorry I misunderstood that. What about this one?

     

    =LET(array,A2:E4,filteredArray,FILTER(array,CHOOSECOLS(array,2)=B6),partialWA,SUMPRODUCT(CHOOSECOLS(filteredArray,4),CHOOSECOLS(filteredArray,5)),partialWA/SUM(CHOOSECOLS(filteredArray,4)))

     

    See also attached.

    • Frederic_J's avatar
      Frederic_J
      Copper Contributor
      May 24, 2024
      It looks promising. I need to test the solution in my application which is more complex than the one I provided to explain the context. Keeping the same data model, could you help me and tell me how I should adapt your formula if I want to add a condition on the region and on the project, using a parameter setting as you did for Country in yellow. But I unfortunately noticed that setting a * in the yellow cell returns a #CALC! mistake. That's a blocking point because I need to find a solution which is wildcard-friendly. Thank you in advance for your support, Martin !
      • djclements's avatar
        djclements
        Bronze Contributor
        May 25, 2024

        Frederic_J One possible wildcard-friendly solution would be to FILTER the data with a custom LAMBDA function, using BYROW and COUNTIF:

         

        LIKE:
=LAMBDA(range,criteria,[is_and_logic],
    BYROW(range, LAMBDA(row,
        LET(
            results, COUNTIF(row, criteria),
            IF(is_and_logic, PRODUCT(results), SUM(results)))
        )
    )
)

         

        TEXTSPLIT can also be used with this method to accept multiple values, separated by a delimiter (e.g. "; "), for each of the criteria fields in your dataset (Region, Country and Project).

         

        Furthermore, IF / ISBLANK can be used to specify a default value if one of the criteria cells is blank:

         

        NZ:
=LAMBDA(range,value_if_blank,
    IF(ISBLANK(range), value_if_blank, range)
)

         

        For example, if the criteria cells for Region, Country and Project were H1, H2 and H3 respectively, the formula to calculate the weighted average would be:

         

        =LET(
    arr, FILTER(D2:E12,
        LIKE(A2:A12, TEXTSPLIT(NZ(H1, "<>λ"), "; "))*
        LIKE(B2:B12, TEXTSPLIT(NZ(H2, "<>λ"), "; "))*
        LIKE(C2:C12, TEXTSPLIT(NZ(H3, "<>λ"), "; "))
    ),
    IFERROR(SUMPRODUCT(TAKE(arr,, 1), TAKE(arr,,-1)) / SUM(TAKE(arr,, 1)), 0)
)

         

        Please see the attached demo workbook...

         

        For more information:

Resources