If Statement for Visible Cells Only

Question

I am creating a form that autopopulates data when another form is pasted onto the workbook. I combined numbers from 5 different cells into one cell using this formula

=TEXT(L2,IF(L2=0," ","$000,000"))&"; "&TEXT(L3,IF(L3=0," ","$000,000"))&"; "&TEXT(L4,IF(L4=0," ","$000,000"))&"; "&TEXT(L5,IF(L5=0," ","$000,000"))&"; "&TEXT(L6,IF(L6=0," ","$000,000")).

Sometimes I will need to hide 3 rows, meaning I only need 2 of the numbers combined. The problem is that it still captures the 5 numbers. Is there a way to combine the visible cells only?

if you put an IF statement for each add on. IF(ISBLANK(L3),"","; "&TEXT(L3,IF(L3=0," ","$000,000")). Just repeat that check in each location that you know may come up blank. — Forward Ed, Jun 29 '18 at 21:23

score 1 · Answer 1 · answered Jun 29 '18 at 21:57

1

If you want to skip the blank cells use TEXTJOIN():

=TEXTJOIN("; ",TRUE,if(l2:l6<>"",TEXT(L2:L6,"$000,000"),""))

Use as an array formula, confirm with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

answered Jun 29 '18 at 21:57

Scott Craner

148,073
10
49
81

[TEXTJOIN for xl2010/xl2013 with criteria](https://stackoverflow.com/questions/50716550/textjoin-for-xl2010-xl2013-with-criteria/50719050?s=1|83.5956#50719050) – Jun 30 '18 at 10:40
`=TEXTJOIN("; ", TRUE, TEXT(L2:L6,"$000,000;;;"))` with CSE. – Jun 30 '18 at 10:49

If Statement for Visible Cells Only

1 Answers1