Formula exceeded the number of characters allowed. |General Excel Questions & Answers|Excel Forum|My Online Training Hub

Wow, Inga! That is the biggest formula I've ever seen!

First, if you ever find yourself writing a formula that's even 1/4 of that size, you should stop and find a better way.

It's very difficult to understand what a formula of this size is doing without an example file, but what I can say is:

There is a lot of redundancy in this formula with repeated IFERROR functions that you don't need. You can also use the LET function to declare variables you can then use repeatedly.

For example, taking just the first section of the formula for $P$1, you can simplify it to this:

=LET(
   LookupResult10, VLOOKUP($B23, Lookups!$C:$M, 10, FALSE),
   LookupResult11, VLOOKUP($B23, Lookups!$C:$M, 11, FALSE),
   LookupResult7, VLOOKUP($B23, Lookups!$C:$M, 7, FALSE),
   LookupResult8, VLOOKUP($B23, Lookups!$C:$M, 8, FALSE),
   LookupResult4, VLOOKUP($B23, Lookups!$C:$M, 4, FALSE),
   LookupResult5, VLOOKUP($B23, Lookups!$C:$M, 5, FALSE),
   LookupResult3, VLOOKUP($B23, Lookups!$C:$M, 3, FALSE),
   PivotData, IFERROR(GETPIVOTDATA("Sum of Hours", Pivot!$A$3, "Transaction Date", $P$1, "Personnel Desc", $B23, "Pay Code", AB$22), 0),
  

    IF(
       LookupResult10 <> "",
       IF($P$1 >= LookupResult11, (LookupResult10 + AB$20) * AB$21 * PivotData, (LookupResult7 + AB$20) * AB$21 * PivotData),
       IF(
           LookupResult7 <> "",
           IF($P$1 >= LookupResult8, (LookupResult7 + AB$20) * AB$21 * PivotData, (LookupResult4 + AB$20) * AB$21 * PivotData),
           IF(
               LookupResult4 <> "",
               IF($P$1 >= LookupResult5, (LookupResult4 + AB$20) * AB$21 * PivotData, (LookupResult3 + AB$20) * AB$21 * PivotData),
               (LookupResult3 + AB$20) * AB$21 * PivotData
           )
       )
   )
)

Explanation of Changes:

1. Defined Variables: Using
   LET

   , I defined each

   VLOOKUP

   result as a separate variable, such as

   LookupResult10

   ,

   LookupResult11

   , etc., to avoid redundant calls to the

   VLOOKUP

   function.

2. Simplified
   IFERROR
   : I wrapped the
   GETPIVOTDATA

   function in a single

   IFERROR

   at the variable definition stage (

   PivotData

   ) instead of repeatedly using it within the formula.

3. Final Calculation: The formula performs the core calculations using the pre-defined variables, significantly improving readability and efficiency.

However, there appears to be another pattern you can exploit: every criteria cell ($P$1, $Q$1, $R$1, $S$1, $T$1) is being tested to see if it's bigger than the lookup value returned, so couldn't you do a COUNTIFS against this criteria:

=COUNTIFS($P$1:$U$1,">="&VLOOKUP($B23,Lookups!$C:$M,11,FALSE))

and then multiply the count by this part:

VLOOKUP($B23,Lookups!$C:$M,10,FALSE)+AB$20)*AB$21)*(IFERROR(GETPIVOTDATA("Sum of Hours",Pivot!$A$3,"Transaction Date",$R$1,"Personnel Desc",$B23,"Pay Code",AB$22),0)))

Another option is to create separate formulas for each pattern in their own columns and then bring them together in one final formula. 

There is no shame in breaking the formula down into separate elements. In fact, this is smart formula writing as those in the know understand that Excel can calculate faster this way. Plus, it will make troubleshooting easier. 

If you're still stuck, take some time to create a dummy file where we can see your formula insitu and an explanation of how it's supposed to calculate so we can give you a more complete answer.

Mynda