IF Statement - need to reduce size drastically

[tlc53]

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!

 

[Norie]

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]

That's a good idea! Thank you!
I've taken out all the large IF Statements but it hasn't reduced the size yet 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