I'm trying to add certain cells together depending on what month a particular cell is showing. The formula gives me an error unless I cut it down.

Could my formula be too long? If so, suggestions on what other options there are? It would be easier to use an "IN" type of statement but I don't think that works in an IF in Excel.

Here's my IF statement:

=IF((MONTH(N$5))=1,N6,IF((MONTH(N$5))=2,M6+N6,IF(( MONTH(N$5))=3,L6+M6+N6,IF((MONTH(N$5))=4,N6,IF((MO NTH(N$5))=5,M6+N6,IF((MONTH(N$5))=6,L6+M6+N6,IF((M ONTH(N$5))=7,N6,IF((MONTH(N$5))=8,M6+N6,IF((MONTH( N$5))=9,L6+M6+N6,IF((MONTH(N$5))=10,N6,IF((MONTH(N $5))=11,M6+N6,IF((MONTH(N$5))=12,L6+M6+N6, "ERROR"))))))))))))

Got it from SteveG over at excelforum.com!

Posting here for the people that might search it.

=IF(ISERROR(MONTH(N5)),"Error",IF(ISBLANK(N5),"",CHOOSE(MONTH(N5),N6,M6+N6,L6+M6+N6,N6,M6+N6,L6+M6 +N 6,N6,M6+N6,L6+M6+N6,N6,M6+N6,L6+M6+N6)))

Your formula is 318 characters including spaces, Excel doesn't like to evaluate that many. Try a vlookup to get your varable. It looks like you are only using N or M+N or L+M+N. Try an simplifying your if statement. You should only have three choices. Use an Or statement, =IF(OR(B1="ok",B1="good"),"ok","not")

Technically, a formula may contain 1024 characters. However, if a portion of the formula is another "complete formula" then the total limit goes down, often 255 characters.

use the lookup formula (you can reference any other sheet)...much easier...or you choose()...depending on what you are doing. It looks like you are calculating a range of columns based upon a certain input (probably a period range or month number) to get a year-to-date amount. I'd use =choose() for this function.

I'm trying to add certain cells together depending on what month a particular cell is showing. The formula gives me an error unless I cut it down.

Could my formula be too long? If so, suggestions on what other options there are? It would be easier to use an "IN" type of statement but I don't think that works in an IF in Excel.

Here's my IF statement:

=IF((MONTH(N$5))=1,N6,IF((MONTH(N$5))=2,M6+N6,IF(( MONTH(N$5))=3,L6+M6+N6,IF((MONTH(N$5))=4,N6,IF((MO NTH(N$5))=5,M6+N6,IF((MONTH(N$5))=6,L6+M6+N6,IF((M ONTH(N$5))=7,N6,IF((MONTH(N$5))=8,M6+N6,IF((MONTH( N$5))=9,L6+M6+N6,IF((MONTH(N$5))=10,N6,IF((MONTH(N $5))=11,M6+N6,IF((MONTH(N$5))=12,L6+M6+N6, "ERROR"))))))))))))



I think Excel only supports upto 9 nested 'IF's

Excel supports 7 nested IFs. However, by using named formulas, I have nested 13 IF statements. Not pretty, not easy to trouble shoot. It is much better/easier/more reliable/expandable to use lookup tables.

=if(mod(month(n5),3)=1,n6,if(mod(month(n5),3)=2,m6 +n6,if(mod(month(n5),3)=0,l6+m6+n6,"error")))

--- --- ---

EDIT:
Actually, it might even be easier than that, if you guarantee the entries will always be dates:

=if(isblank(n6),"",if(mod(month(n5),3)=1,n6,if(mod(month(n5),3)=2,m6 +n6,l6+m6+n6)))