Trying to Shorten a Long Nested If Statement

BTW, the above formula assumes none of the cells will be a negative number - is that correct? If they can be negative, then how would you want to handle it? Let's say g6 = -23 and h6 = 0, what value would you want to display? And would if they were reversed (g6 = 0 and h6 = -23), would you want a different answer?

@Greg, no IFS in excel 2013 Also, looking at the order of the logic tests in the original, I think MIN might need to be MAX

Perahps

=IF(SUMPRODUCT((D6:R6<50)+(D6:R6=1000)),SUMPRODUCT(D$4:R$4*D6:R6))

This is assuming that غ = 1000 and that I have read the formula correctly.

If "IFS" is not available to the OP, then you could use this formula:

=IF(MIN(D6:R6)=0,"غ",IF(MIN(D6:R6)<50,0,SUMPRODUCT(D4:R4,D6:R6)))

However, as FDIBBONS has noted, we need to know exactly what the poster wants. Because, the statement he currently has really doesn't make sense as it says if D6<50, then be 0, but if it's 0, the be غ. Well, it could never by غ because 0 is less than 50 so it would never get to that part of his IF statement.

For the OP, what my formula says is, if any cell in row 6 is 0, then be غ. If none are 0, but if any are less than 50, then be 0, else, sum the products of the 2 rows.

Cell q6 has a function that adds a product of (b6 * b4), (c4 * c6), (d6 * d4), and so on (p6 * p4)
Provided the result is zero in two cases: enter a value less than 50
Or enter a literal value

A shortcut is required for this function to function the same as the existing function

=IF(B6<50,0,IF(B6="غ",0,IF(C6<50,0,IF(C6="غ",0,IF(D6<50,0,IF(D6="غ",0,IF(E6<50,0,IF(E6="غ",0,IF(F6<50,0,IF(F6="غ",0,IF(G6<50,0,IF(G6="غ",0,IF(H6<50,0,IF(H6="غ",0,IF(I6<50,0,IF(I6="غ",0,IF(J6<50,0,IF(J6="غ",0,IF(K6<50,0,IF(K6="غ",0,IF(L6<50,0,IF(L6="غ",0,IF(M6<50,0,IF(M6="غ",0,IF(N6<50,0,IF(N6="غ",0,IF(O6<50,0,IF(O6="غ",0,IF(P6<50,0,IF(P6="غ",0,SUM(B6*$D$4,C6*$E$4,D6*$F$4,E6*$G$4,F6*$H$4,G6*$I$4,H6*$J$4,I6*$K$4,J6*$L$4,K6*$M$4,L6*$N$4,M6*$O$4,N6*$P$4)))))))))))))))))))))))))))))))

Wow, I'm unfamiliar with that character - غ - but it does funny things to my screen. When I look at your formula on this web page, it is different than when I paste it into Excel and see it as a formula there. When I look at it on this page, it basically says that if in row 6 the value is zero, to make the value غ. But when I paste it into excel, it says that if in row 6 the value is غ, to make the cell value 0.

Anyway, so I'm replacing that symbol with the letter "x". Based on what I can now interpret by what you are saying, does this work for you? (I'm still using D6:R6 as the range as that is what you had in the original post, but your last post mentions B6:P6, which is a little confusing).

=IF(OR(MIN(D6:R6)<50,COUNTIF(D6:R6,"x")>0,SUMPRODUCT(D4:R4,D6:R6))