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