SUMPRODUCT with * wildcard

Question

Hello,&nbsp;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 synthetize the values for a particular project or a particular country or region (in which case a weighted average is necessary therefore the sumproduct came to my mind). Unfortunately, the sumproduct works well for a given project but if I need to get the synthesis for a country or region, I need to declare the wildcard * for the project and in that case, the sumproduct does not work.&nbsp; I found many posts indicating a partial solution (like ISNUMBER(FIND(xxxxx))), but none of them brings an answer to my problem and I cannot believe there is no smart solution for that simple issue.&nbsp;Table definition (CSV):Region, Country, Project, Turnover, MarginEurope, Slovakia, ABC, 10, 5%Europe, Slovakia, ABD, 15, 2%Europe, Poland, ABB, 20, 12%&nbsp;I need a formula which returns the weighted margin depending on the selected region, country or project.&nbsp;Could you please help me ?&nbsp;&nbsp;&nbsp;Best regards,&nbsp;Frédéric&nbsp;&nbsp;

martin_angosto · Answer

Frederic_J&nbsp;&nbsp;Hi,&nbsp;Would a simple AVERAGEIF function work to you? Please see attached document.

frederic_j · Answer

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 · Answer

Frederic_J&nbsp;&nbsp;Hi, sorry I misunderstood that. What about this one?&nbsp;=LET(array,A2:E4,filteredArray,FILTER(array,CHOOSECOLS(array,2)=B6),partialWA,SUMPRODUCT(CHOOSECOLS(filteredArray,4),CHOOSECOLS(filteredArray,5)),partialWA/SUM(CHOOSECOLS(filteredArray,4)))&nbsp;See also attached.

frederic_j · Answer

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 · Answer

Frederic_J&nbsp;One possible wildcard-friendly solution would be to FILTER the data with a custom LAMBDA function, using BYROW and COUNTIF:&nbsp;LIKE:
=LAMBDA(range,criteria,[is_and_logic],
    BYROW(range, LAMBDA(row,
        LET(
            results, COUNTIF(row, criteria),
            IF(is_and_logic, PRODUCT(results), SUM(results)))
        )
    )
)&nbsp;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).&nbsp;Furthermore, IF / ISBLANK can be used to specify a default value if one of the criteria cells is blank:&nbsp;NZ:
=LAMBDA(range,value_if_blank,
    IF(ISBLANK(range), value_if_blank, range)
)&nbsp;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:&nbsp;=LET(
    arr, FILTER(D2:E12,
        LIKE(A2:A12, TEXTSPLIT(NZ(H1, "&lt;&gt;λ"), "; "))*
        LIKE(B2:B12, TEXTSPLIT(NZ(H2, "&lt;&gt;λ"), "; "))*
        LIKE(C2:C12, TEXTSPLIT(NZ(H3, "&lt;&gt;λ"), "; "))
    ),
    IFERROR(SUMPRODUCT(TAKE(arr,, 1), TAKE(arr,,-1)) / SUM(TAKE(arr,, 1)), 0)
)&nbsp;Please see the attached demo workbook...&nbsp;For more information:https://openxl.wordpress.com/2023/05/14/how-to-use-wildcards-with-filter-function-in-excel/&nbsp;

Forum Discussion

SUMPRODUCT with * wildcard

Share

Resources