Maximum Character Length of Formula

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=<name1>,B1=<event1>),sumif(C1:C30,"<event1>",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

Report •

April 7, 2009 at 08:01:27
It would seem to be 1024....

See this:


Report •

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:


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 •

April 7, 2009 at 15:37:28
Not sure, but would this work:


There should be some way to combine the Name & Event, then total.....


Report •

Related Solutions

April 7, 2009 at 16:19:25
Another thought,
It would probably be easier if you set it up as
Column A = Names
Column B= Event1
Column C= Event2
Column D= Event3

Then you could do a lookup.


Report •

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 •

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.

Row Number 1 should Have Headings that correspond to the contents of the columns, IE Column A should have heading of "Names"

If I read your post correctly your sheet should have the following basic design...

Column A = Employee Names
Column B = Events
Column C = Amounts to sum

If not, then you'll have to either change the formula, or change your sheet.

Enter the following information in the indicated cells:

Cell F2 First Employee Name
Cell F3 Second Employee Name
Cell F4 Third Employee Name
Cell F5 Fourth Employee Name

Cell G2 Event One, IE Vac.
Cell H2 Event Two, IE Overtime
Cell I2 Event Three, IE Sick
Cell J2 Event Four
Cell K2 Event Five
Cell L2 Event Six
Cell M2 Event Seven

We are going to build a matrix.

Cell F10 First Employee Name
Cell F11 Second Employee Name
Cell F12 Third Employee Name
Cell F13 Fourth Employee Name

Now 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)


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.


Report •

April 8, 2009 at 10:52:27

Thanks for your reponse; I'll give it a try if the other methods don't work.

Brian W

Report •

Ask Question