Multiplying Two ranges in specific condition

**AHB10** · 12-12-2012, 09:12 AM

Good day

I am trying to multiply two ranges in specific condition. I used the following formula and it was working in a perfect way

{=SUMPRODUCT(IF($B$17:$B$505=" Apple ",Q17:Q505),$H$17:$H$505)}

Now, I am trying to add more conditions using the following formula but unfortunately it was not working with me

{=SUMPRODUCT(IF(AND($B$17:$B$505="Apple",$C$17:$C$505="Green"),Q17:Q505),$H$17:$H$505)}

Do you have any suggestion? Could you help me please?

Please note that I am using excel 2010.

Best regards

**benishiryo** · 12-12-2012, 09:31 AM

hi AHB10, welcome to the forum. either without the array:
=SUMPRODUCT(($B$17:$B$22="apple")*($C$17:$C$22="green")*$H$17:$H$22*$Q$17:$Q$22)

or if you want your IFS & array:
=SUMPRODUCT(IF($B$17:$B$22="apple",IF($C$17:$C$22="green",$H$17:$H$22))*$Q$17:$Q$22)

if it doesnt work, uploading a sample Excel file will enable us to help you better. to upload, press "Go Advanced" beside the "Post Quick Reply" button & click on the paperclip icon
ideally, it should contain your desired results

**AHB10** · 12-12-2012, 10:02 AM

Dear benishiryo,

Thank you for your help and the prompt response. In fact, both of your formula works fine. I never though it would that easy.

Thanks again for your Warm welcome

Best regards