Excel formula exceeds 8000 characters limit

0 votes

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(
SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Buy",Transactions!$F$3:$F,"Stock",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"DRIP",Transactions!$F$3:$F,"Stock",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Deposit",Transactions!$F$3:$F,"Stock",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Buy",Transactions!$F$3:$F,"ETF",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"DRIP",Transactions!$F$3:$F,"ETF",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Deposit",Transactions!$F$3:$F,"ETF",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Buy",Transactions!$F$3:$F,"Commodity",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"DRIP",Transactions!$F$3:$F,"Commodity",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Deposit",Transactions!$F$3:$F,"Commodity",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Buy",Transactions!$F$3:$F,"Managed Fund",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"DRIP",Transactions!$F$3:$F,"Managed Fund",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Deposit",Transactions!$F$3:$F,"Managed Fund",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Buy",Transactions!$F$3:$F,"REIT",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"DRIP",Transactions!$F$3:$F,"REIT",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Deposit",Transactions!$F$3:$F,"REIT",Transactions!$D$3:$D,AD3)

-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Sell",Transactions!$F$3:$F,"Stock",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Send",Transactions!$F$3:$F,"Stock",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Sell",Transactions!$F$3:$F,"ETF",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Send",Transactions!$F$3:$F,"ETF",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Sell",Transactions!$F$3:$F,"Commodity",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Send",Transactions!$F$3:$F,"Commodity",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Sell",Transactions!$F$3:$F,"Managed Fund",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Send",Transactions!$F$3:$F,"Managed Fund",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Sell",Transactions!$F$3:$F,"REIT",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Send",Transactions!$F$3:$F,"REIT",Transactions!$D$3:$D,AD3)

+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Buy",Transactions!$F$3:$F,"Crypto",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Crypto Interest",Transactions!$F$3:$F,"Crypto",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Deposit",Transactions!$F$3:$F,"Crypto",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Send",Transactions!$F$3:$F,"Crypto",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Sell",Transactions!$F$3:$F,"Crypto",Transactions!$D$3:$D,AD3)

+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Buy",Transactions!$F$3:$F,"Stablecoin",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Crypto Interest",Transactions!$F$3:$F,"Stablecoin",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Deposit",Transactions!$F$3:$F,"Stablecoin",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Send",Transactions!$F$3:$F,"Stablecoin",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Sell",Transactions!$F$3:$F,"Stablecoin",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$M$3:$M,Transactions!$H$3:$H,"Buy",Transactions!$B$3:$B,AD3)=0,"Sold All",

SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Buy",Transactions!$F$3:$F,"Stock",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"DRIP",Transactions!$F$3:$F,"Stock",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Deposit",Transactions!$F$3:$F,"Stock",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Buy",Transactions!$F$3:$F,"ETF",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"DRIP",Transactions!$F$3:$F,"ETF",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Deposit",Transactions!$F$3:$F,"ETF",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Buy",Transactions!$F$3:$F,"Commodity",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"DRIP",Transactions!$F$3:$F,"Commodity",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Deposit",Transactions!$F$3:$F,"Commodity",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Buy",Transactions!$F$3:$F,"Managed Fund",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"DRIP",Transactions!$F$3:$F,"Managed Fund",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Deposit",Transactions!$F$3:$F,"Managed Fund",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Buy",Transactions!$F$3:$F,"REIT",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"DRIP",Transactions!$F$3:$F,"REIT",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Deposit",Transactions!$F$3:$F,"REIT",Transactions!$D$3:$D,AD3)

-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Sell",Transactions!$F$3:$F,"Stock",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Send",Transactions!$F$3:$F,"Stock",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Sell",Transactions!$F$3:$F,"ETF",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Send",Transactions!$F$3:$F,"ETF",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Sell",Transactions!$F$3:$F,"Commodity",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Send",Transactions!$F$3:$F,"Commodity",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Sell",Transactions!$F$3:$F,"Managed Fund",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Send",Transactions!$F$3:$F,"Managed Fund",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Sell",Transactions!$F$3:$F,"REIT",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Send",Transactions!$F$3:$F,"REIT",Transactions!$D$3:$D,AD3)

+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Buy",Transactions!$F$3:$F,"Crypto",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Crypto Interest",Transactions!$F$3:$F,"Crypto",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Deposit",Transactions!$F$3:$F,"Crypto",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Send",Transactions!$F$3:$F,"Crypto",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Sell",Transactions!$F$3:$F,"Crypto",Transactions!$D$3:$D,AD3)

+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Buy",Transactions!$F$3:$F,"Stablecoin",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Crypto Interest",Transactions!$F$3:$F,"Stablecoin",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Deposit",Transactions!$F$3:$F,"Stablecoin",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Send",Transactions!$F$3:$F,"Stablecoin",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Sell",Transactions!$F$3:$F,"Stablecoin",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$M$3:$M,Transactions!$H$3:$H,"Buy",Transactions!$B$3:$B,AD3)))

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

Feb 21, 2023 in Others by Kithuzzz
 • 38,000 points • 814 views

1 answer to this question.

0 votes

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.

answered Feb 21, 2023 by narikkadan
 • 63,600 points

Related Questions In Others

0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
 • 13,690 points • 1,053 views
0 votes
0 answers

MS Excel - SumProduct formula with Loop

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

Feb 18, 2022 in Others by Edureka
 • 13,690 points • 419 views
0 votes
1 answer

Excel formula to check date within this week and last week

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

answered Sep 29, 2022 in Others by narikkadan
 • 63,600 points • 1,939 views
0 votes
1 answer

Excel COUNTIF formula

Please see MS Excel: COUNTIF Function (WS) You should ...READ MORE

answered Sep 29, 2022 in Others by narikkadan
 • 63,600 points • 786 views
0 votes
1 answer

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

answered Sep 30, 2022 in Others by narikkadan
 • 63,600 points • 850 views
0 votes
1 answer

Moving Average formula in Excel not autofilling in table

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

answered Oct 2, 2022 in Others by narikkadan
 • 63,600 points • 1,294 views
0 votes
1 answer

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

answered Oct 3, 2022 in Others by narikkadan
 • 63,600 points • 1,976 views
0 votes
1 answer

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

answered Oct 8, 2022 in Others by narikkadan
 • 63,600 points • 995 views
0 votes
1 answer

Way to overcome Excel Vlookup function limit of 256 characters

If you are using VLOOKUP like this: =VLOOKUP(A2,D2:Z10,3,FALSE) i.e. ...READ MORE

answered Sep 30, 2022 in Others by narikkadan
 • 63,600 points • 3,518 views
0 votes
1 answer

Excel - IF Formula with a FIND

What about using mid() to see if ...READ MORE

answered Sep 27, 2022 in Others by narikkadan
 • 63,600 points • 720 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP
"PMP®","PMI®", "PMI-ACP®" and "PMBOK®" are registered marks of the Project Management Institute, Inc. MongoDB®, Mongo and the leaf logo are the registered trademarks of MongoDB, Inc.