Product if meets 2 criteria

[nat_fpa3]

I'm trying to get the product of a range of numbers if it meets two criterion on a different tab. The formula below isn't working for me, can anyone see what I'm doing wrong?


{=PRODUCT(IF(AND('Online Inputs'!$B$2:$B$337=S32,'Online Inputs'!$C$2:$C$337=T32),'Online Inputs'!E$2:E$337))}

Thanks for any help!

 

[AliGW]

How about this?

{=IF(AND('Online Inputs'!$B$2:$B$337=S32,'Online Inputs'!$C$2:$C$337=T32),PRODUCT('Online Inputs'!E$2:E$337))}

 

[nat_fpa3]

I tried that and it just gives me "FALSE", not product of the values that meet the criterion. Thanks though!

 

[AliGW]

Perhaps read up on the SUMPRODUCT function?

http://www.contextures.com/xlFunctions01.html

 

[Aladin Akyurek]

I'm trying to get the product of a range of numbers if it meets two criterion on a different tab. The formula below isn't working for me, can anyone see what I'm doing wrong?


{=PRODUCT(IF(AND('Online Inputs'!$B$2:$B$337=S32,'Online Inputs'!$C$2:$C$337=T32),'Online Inputs'!E$2:E$337))}

Thanks for any help!

Control+shift+enter, not just enter:

=PRODUCT(IF('Online Inputs'!$B$2:$B$337=S32,IF('Online Inputs'!$C$2:$C$337=T32,'Online Inputs'!E$2:E$337)))

 

[Marcelo Branco]

I'm trying to get the product of a range of numbers if it meets two criterion on a different tab. The formula below isn't working for me, can anyone see what I'm doing wrong?


{=PRODUCT(IF(AND('Online Inputs'!$B$2:$B$337=S32,'Online Inputs'!$C$2:$C$337=T32),'Online Inputs'!E$2:E$337))}

Thanks for any help!

In array formulas with two or more conditions you cannot use AND this way.

Instead, try nested IFs like this
=PRODUCT(IF('Online Inputs'!$B$2:$B$337=S32,IF('Online Inputs'!$C$2:$C$337=T32,'Online Inputs'!E$2:E$337)))
Ctrl+Shift+Enter

or multiply the conditions like
=PRODUCT(IF(('Online Inputs'!$B$2:$B$337=S32)*('Online Inputs'!$C$2:$C$337=T32),'Online Inputs'!E$2:E$337))
Ctrl+Shift+Enter

Hope this helps

M.