I have a formula with 8433 characters, however, it seems that Excel only allows for 8192. With more than 70 conditions, the "if" statement is extensive. Is there a more effective approach to analyse the criteria so that the formula below uses fewer characters?

```=IF(ISBLANK(AD3),"",IF(

```

I tried to save the workbook in excel binary workbook format, but macros don't work

Feb 21, 2023 in Others 486 views

## 1 answer to this question.

Using auxiliary columns to divide the formula into smaller sections is one method for reducing the formula's character count. Here's a sample of how you might go about doing that:

1. In a new column, use the formula =IF(ISBLANK(AD3),"",1) to create a flag column that marks the rows where the formula should be applied.
2. In another column, use the formula =SUMIFS(Transactions!\$I\$3:\$I,Transactions!\$H\$3:\$H,"Buy",Transactions!\$F\$3:\$F,"Stock",Transactions!\$D\$3:\$D,AD3) to calculate the first sum value.
3. Repeat the previous step for the remaining 18 sum values.
4. In a final column, use the formula =SUMIFS(Transactions!\$I\$3:\$I,Transactions!\$H\$3:\$H,"Sell",Transactions!\$F\$3:\$F,"Stock",Transactions!\$D\$3:\$D,AD3) to calculate the first subtraction value.
5. Repeat the previous step for the remaining 8 subtraction values
6. Finally, in a separate cell,use the following formula to calculate the final result: =SUMPRODUCT(SumRange)-SUMPRODUCT(SubtractionRange), where SumRange is the range containing the 19 sum values, and SubtractionRange is the range containing the 9 subtraction values.

This approach should allow you to reduce the overall character count of the formula, as well as make it easier to read and maintain.

• 63,420 points

## MS Excel - SumProduct formula with Loop

1 I have 4 arrays of data where ...READ MORE

## Excel formula to check date within this week and last week

Assuming the first date in A2 try this formula ...READ MORE

## Is there a maximum number of formula fields allowed in Excel (2010)

See http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx for limits on specs it doesn't indicate ...READ MORE

## Moving Average formula in Excel not autofilling in table

You could use AVERAGEIFS to make the ...READ MORE

## Excel Formula Query on Summing Annual Leave Days and Half Days

Try Something like =SUM(COUNTIF(\$A\$1:\$A\$8,"A"),COUNTIF(\$A\$1:\$A\$8,"HA")/2) - total count ...READ MORE

## INDEX formula in Excel, Top 10, repeats previous value

Try this formula in cell W4: =IF(V3=V4,INDEX(INDIRECT("I"&MATCH(W3,I:I,0)+1&":I26"),MATCH(V4,INDIRECT("R"&MATCH(W3,I:I,0)+1&":R26"),0)),INDEX(\$I\$2:\$I\$26,MATCH(V4,\$R\$2:\$R\$26,0))) The calculation ...READ MORE