How to execute sumproduct with two criteria?

0
votes

I need to create a formula in excel that returns the sum-product of two columns in a range, based on two conditions. Considering a table of 4 columns (A;B;C;D), if any row in column A is equal to a given value (example="A") and any row in column B starts with "*", then multiply column C and D and sum these products. Any suggestions on how to implement the formula?

I thought it could be the following formula but it doesn't work:

=SUMPRODUCT(--(A2:A12="A"); --(left(B2:B12;1)="*"); C2:C12; D2:D12)
1
excelexcel-formula

1 Answers

0
votes

You could use an array formula such as :

{=SUM((A2:A12="A")*(LEFT(B2:B12)="*")*C2:C12*D2:D12)}

Remember to validate with ctrl+shift+enter