SUMPRODUCT with * wildcard

[Frederic_J]

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 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.  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.

Table definition (CSV):

Region, Country, Project, Turnover, Margin

Europe, Slovakia, ABC, 10, 5%

Europe, Slovakia, ABD, 15, 2%

Europe, Poland, ABB, 20, 12%

I need a formula which returns the weighted margin depending on the selected region, country or project.

Could you please help me ?

Best regards,

Frédéric

[#2]

@Frederic_J 

Hi,

Would a simple AVERAGEIF function work to you? Please see attached document.

[#3]

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).

[#4]

@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.

[#5]

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 !

[#6]

@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:

• https://openxl.wordpress.com/2023/05/14/how-to-use-wildcards-with-filter-function-in-excel/