IF Statement - need to reduce size drastically

tlc53

Active Member
Joined
Jul 26, 2018
Messages
365
Hi,

I have a spreadsheet which I have created large IF Statements in. It was all good until I tried to open it again and it took 10 minutes to open! I then realised the size has increased to 10k KB!

Here is one of my multiple IF Statements (one of the smaller ones). In column B are 3 digit codes, which I would like the value returned if the IF Statement returns True. In this case, it returns "260, 261, 262, " which is the result of the first 3 IF Statements returning True.

=IF(OR(AND(I25>=25%,J25=""),AND(G25=0,F25>0,J25="")),B25&", ","")&IF(OR(AND(I26>=25%,J26=""),AND(G26=0,F26>0,J26="")),B26&", ","")&IF(OR(AND(I27>=25%,J27=""),AND(G27=0,F27>0,J27="")),B27&", ","")&IF(OR(AND(I28>=25%,J28=""),AND(G28=0,F28>0,J28="")),B28&", ","")&IF(OR(AND(I29>=25%,J29=""),AND(G29=0,F29>0,J29="")),B29&", ","")&IF(OR(AND(I30>=25%,J30=""),AND(G30=0,F30>0,J30="")),B30&", ","")&IF(OR(AND(I31>=25%,J31=""),AND(G31=0,F31>0,J31="")),B31&", ","")&IF(OR(AND(I32>=25%,J32=""),AND(G32=0,F32>0,J32="")),B32&", ","")&IF(OR(AND(I33>=25%,J33=""),AND(G33=0,F33>0,J33="")),B33&", ","")&IF(OR(AND(I34>=25%,J34=""),AND(G34=0,F34>0,J34="")),B34&", ","")

Is there a way to reduce this?? This is my attempt but it isn't valid/correct, nor do I know if it is possible.

=IF(OR(AND(COUNTIFS(I25:I34>=25%,J25:J34="")),AND(COUNTIFS(G25:G34=0,F25:F35>0,J25:J34=""))),B25:B34&", ","")

Appreciate your help. Thanks!
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,541
Office Version
365
Platform
Windows
Do you need to do everything in one cell?

You could have a column with a formula like this copied down,

=IF(OR(AND(I25>=25%,J25=""),AND(G25=0,F25>0,J25="")),B25,"")

which would return either the value in column B or an empty string based on the values in the other columns.

You could then have formula to concatenate the values returned from the formula above into a list.
 

tlc53

Active Member
Joined
Jul 26, 2018
Messages
365
That's a good idea! Thank you!
I've taken out all the large IF Statements but it hasn't reduced the size yet :confused: I think I might need to start from scratch.

I'm using TEXTJOIN instead of CONCATENATE and it seems to be doing the trick.

Thanks so much for all your help :)
 

Watch MrExcel Video

Forum statistics

Threads
1,095,688
Messages
5,445,959
Members
405,372
Latest member
Vithanalas

This Week's Hot Topics

Top