# Maximum Character Length of Formula

Dell / INSPIRION
April 7, 2009 at 05:58:15
Specs: Windows XP, 1.0 Gb
 I need to write IF/AND/SUMIF formulas that include the names of four employees and eight events, i.e., vacation. This would be a total of 32 separate statements combined into one. I know I can use "&" to join the individual statements together as only seven IF statements can be nested together.Here is an sample of what I propose to use:=if(and(A1=,B1=),sumif(C1:C30,"",D1:D30)) & .....As 32 of these statements combined into one would be quite long, is there a maximum number of characters that can be used? Is there a more efficent way to write this combined formula?Thank you.

See More: Maximum Character Length of Formula

#1
April 7, 2009 at 08:01:27
 It would seem to be 1024....See this:http://support.microsoft.com/kb/264626MIKEhttp://www.skeptic.com/

Report •

#2
April 7, 2009 at 09:42:06
 re: Is there a more efficent way to write this combined formula?That's tough to answer without knowing exactly what you are trying to do. My gut feeling is that if you are concerned about exceeding the maximum length of a formula, then there is probably something you could do differently.As far as simply shortening the formula, there's probably many ways.For example, you could use named ranges instead of the actual names and events.Let's say you have the names of the employees in a range of cells. e.g. Employee # 1, Tom Jones, is in A17. If you Name A17 to be something like Emp1, you could use:=IF(A1=Emp1, TRUE,FALSE) instead of=IF(A1="Tom Jones", TRUE,FALSE)That also gives you the advantage of being able to change the name in A17 without having to edit your formula.

Report •

#3
April 7, 2009 at 15:37:28
 Not sure, but would this work:=sumif(C1:C30,A1&B1,D1:D30))There should be some way to combine the Name & Event, then total.....MIKE

Report •

Related Solutions

#4
April 7, 2009 at 16:19:25
 Another thought,It would probably be easier if you set it up asColumn A = NamesColumn B= Event1Column C= Event2Column D= Event3Then you could do a lookup.MIKE

Report •

#5
April 8, 2009 at 03:32:35
 Mike and DerbyDad03,Thanks for the information; I'm going to try to use NAMED RANGES and/or LOOKUPS.Brian W

Report •

#6
April 8, 2009 at 09:15:23
 The following is very ugly and very clunky.....I would advise NOT using this...Find a different design for your spreadsheet but if you absolutely, positively need to keep it the way it is then here is a solution.It can be configured differently, but I choose this way.First:Row Number 1 should Have Headings that correspond to the contents of the columns, IE Column A should have heading of "Names"Second:If I read your post correctly your sheet should have the following basic design...Column A = Employee NamesColumn B = EventsColumn C = Amounts to sumIf not, then you'll have to either change the formula, or change your sheet.Third:Enter the following information in the indicated cells:Cell F2 First Employee NameCell F3 Second Employee NameCell F4 Third Employee NameCell F5 Fourth Employee NameCell G2 Event One, IE Vac.Cell H2 Event Two, IE OvertimeCell I2 Event Three, IE SickCell J2 Event FourCell K2 Event FiveCell L2 Event SixCell M2 Event SevenFourth:We are going to build a matrix.Cell F10 First Employee NameCell F11 Second Employee NameCell F12 Third Employee NameCell F13 Fourth Employee NameNow comes the good part:Cell G10 enter the formula: =SUMPRODUCT(--(\$A\$2:\$A\$101=\$F2),--(\$B\$2:\$B\$101=G\$2), \$C\$2:\$C\$101)Fifth: Drag the formula Down the four names, Drag the formula Across the seven events.You should now have a matrix of Four Names and Seven Events with totals for all possible combinations.MIKEhttp://www.skeptic.com/

Report •

#7
April 8, 2009 at 10:52:27
 Mike,Thanks for your reponse; I'll give it a try if the other methods don't work.Brian W

Report •