IF with SUMPRODUCT error

**ddub25** · 02-15-2012, 06:04 AM

IF(C45<>"",C45,
IF(D45="","--",
IF(MONTH($T45)<>MONTH(N1),"D",
IF(SUMPRODUCT(--(P45:P50=D45),--(Q45:Q50=E45),--(R45:R50=G45),--(S45:S50="D")),"D",
IF(SUMPRODUCT(--(P45:P50=D45),--(Q45:Q50=E45),--(R45:R50=G45),--(S45:S50="A")),"P",
IF(SUMPRODUCT(--(P45:P50=D45),--(Q45:Q50=E45),--(R45:R50=G45),--(S45:S50="B")),"P",
IF(SUMPRODUCT(--(P45:P50=D45),--(Q45:Q50=E45),--(R45:R50=G45),--(S45:S50="C")),"P",
IF(SUMPRODUCT(--(P45:P50=D45),--(Q45:Q50=E45),--(R45:R50=G45),--(S45:S50="ZZZ")),"ZZZ",
"D"))))))))

Can anyone help me with this formula? It worked fine until I added the 3rd line, IF(MONTH($T45)<>MONTH(N1),"D",
When I copy and paste the formula I am not given any error message, instead the formula just becomes entered as text (I can see the formula in the cell rather than a result).

Thanks.

Dan

**zbor** · 02-15-2012, 06:08 AM

This doesn't have much sence.

Upload example workbook andd write yhat you trying to do and what's your expected result.

**Fotis1991** · 02-15-2012, 06:11 AM

Hi Dan

When I copy and paste the formula I am not given any error message, instead the formula just becomes entered as text (I can see the formula in the cell rather than a result).

Maybe, when you copy-paste the formula, also copy the "=" signal?? Copy and paste the formula without = and put this in the paste cell.....

**teylyn** · 02-15-2012, 07:24 AM

In Excel 2003 and earlier you cannot nest more than 7 IF() functions. Split the logic into different cells.

Or try

=IF(C45<>"",C45,
IF(D45="","--",
IF(MONTH($T45)<>MONTH(N1),"D",
IF(SUMPRODUCT(--(P45:P50=D45),--(Q45:Q50=E45),--(R45:R50=G45),--(S45:S50="D")),"D",
IF(OR(
       SUMPRODUCT(--(P45:P50=D45),--(Q45:Q50=E45),--(R45:R50=G45),--(S45:S50="A")),
       SUMPRODUCT(--(P45:P50=D45),--(Q45:Q50=E45),--(R45:R50=G45),--(S45:S50="B")),
       SUMPRODUCT(--(P45:P50=D45),--(Q45:Q50=E45),--(R45:R50=G45),--(S45:S50="C"))),"P",
IF(SUMPRODUCT(--(P45:P50=D45),--(Q45:Q50=E45),--(R45:R50=G45),--(S45:S50="ZZZ")),"ZZZ",
"D"))))))

cheers,