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.

It would seem to be 1024.... See this: http://support.microsoft.com/kb/264626 MIKE http://www.skeptic.com/

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.

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 http://www.skeptic.com/

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. MIKE http://www.skeptic.com/

Mike and DerbyDad03, Thanks for the information; I'm going to try to use NAMED RANGES and/or LOOKUPS. Brian W

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 Names Column B = Events Column C = Amounts to sum If 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 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 Fourth: 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) 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. MIKE http://www.skeptic.com/