Multiply IF with multiple conditions

[noonia]

Good Evening,

today I came across the following problem:

Say we have a table where the a-column is for example the color of a car, the b-column who sold the car and the c-column is the price it sold for.We now want to multiply the values of all red cars sold by Peter and here lies the problem.
When doing this with only 1 condition, say color, I would use =PRODUCT(IF(A1:A10="red",C1:C10,"")) and it would work perfectly fine, however as soon as 2 or more conditions come into play my approach was =PRODUCT(IF(AND(A1:A10="red";B1:B10="Peter");C1:C10;"")) but for some reason this does not work.

If both conditions are met it still works, if at least one is not it will return a #VALUE error and I don't understand why because the result of the test should be in the same format, shouldn't it?

best regards

 

[Peter_SSs]

Welcome to the MrExcel board!

Instead of the AND() structure, try stringing the conditions in IF() functions like this (still array-entered)

{=PRODUCT(IF(A1:A10="red",IF(B1:B10="Peter",C1:C10)))}

 

[Marcelo Branco]

Welcome to Mr Excel forum

To use AND condition in array formulas you should multiply the conditions
Try
=PRODUCT(IF((A1:A10="red")*(B1:B10="Peter"),C1:C10,""))
Ctrl+Shift+Enter

EDIT: or use nested IFs as suggested by Peter

M.

 

[noonia]

Worked perfectly fine, thank you so much!

 

[Peter_SSs]

Worked perfectly fine, thank you so much!

You're welcome. Thanks for letting us know.