Forum Discussion
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...
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 !
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: