Need Assistance with IF, AND, SUMPRODUCT Nested Formula

**eparr1026** · 07-02-2013, 02:59 PM

Hi all, I am in desperate need of assistance in making this formula work. It does work properly if I just make a simple calculation that satisfies the AND requirements such as a1*b1. But when I add the SUMPRODUCT I get nothing but #VALUE! as a result. Can anyone please assist me in making this formula work??

Thanks so much for your assistance.

=IF((AND(Q20="No",R20="No")),SUMPRODUCT($V$15:$GD$15,V20:GD20,IF((AND(Q20="Yes",R20="No")),SUMPRODUCT($V$16:$GD$16,V20:GD20,IF((AND(Q20="Yes",R20="Yes")),SUMPRODUCT($V$15:$GD$15,V20:GD20," "))))))

**XOR LX** · 07-02-2013, 03:55 PM

Wow! A multi-nested SUMPRODUCT formula!

No, but seriously, that looks a little iffy on first impressions. Firstly, are you sure that you actually want this nesting (which I'm not even sure means much), or should it perhaps be:

=IF((AND(Q20="No",R20="No")),SUMPRODUCT($V$15:$GD$15,V20:GD20),IF((AND(Q20="Yes",R20="No")),SUMPRODUCT($V$16:$GD$16,V20:GD20),IF((AND(Q20="Yes",R20="Yes")),SUMPRODUCT($V$15:$GD$15,V20:GD20)," ")))))

Secondly, what exactly are you trying to do with this formula? Can you post a workbook indicating your desired results?

Regards

**rcm** · 07-02-2013, 03:58 PM

would this be it?

Please Login or Register  to view this content.

**eparr1026** · 07-02-2013, 04:50 PM

My intent is to multiply cell J11 by either J7, J8 or J9 depending on the values in E11 and F11 and I can make that work (See formula in cell FT11. My problem is that I need to multiply Row 11 by the appropriate row 7, 8 or 9, once again, depending on the values in E11 and F11 but I need to make that work for columns J through FR and arrive at a grand total in column FT. Attached is a sample condensed file of what I am trying to accomplish. Hope that makes sense.

Thanks!!!!

**eparr1026** · 07-03-2013, 02:57 PM

Much thanks to XOR LX!! Your suggestion worked perfectly!!

Really appreciate your help!!

**Ace_XL** · 07-03-2013, 03:08 PM

A tad shorter version of XOR LX's formula perhaps..

=IF(OR(AND(Q20="No",R20="No"),AND(Q20="Yes",R20="Yes")),SUMPRODUCT($V$15:$GD$15,V20:GD20),IF(AND(Q20="Yes",R20="No"),SUMPRODUCT($V$16:$GD$16,V20:GD20)," "))