Two Formulas in one cell. Need to remove the return zero

[itsgrady]

=IFNA(IFS($D$3=$S$3,T5,$D$3=$U$3,V5),"") :Returns information in one column. The next day the other information goes in the column.

Returns a zero when empty need to remove the zero. The formula works well but for the zero when blank. If was able to remove zero for only day but not for the other day.

=IFNA(IF(T5="","",IFS($D$3=$S$3,T5,$D$3=$U$3,V5),"") )


The sheet has sixty rows with information in both S and U columns where the information stored for a daily form. The formula works well but it returns a zero when there is not information in a given cell in either column. The information goes in one column depending on the day it will pull information from one the columns in the calculation. I need different information each day and the two columns have different information but for the same form on a given day.

=IFNA(IFS($D$3=$S$3,T5,$D$3=$U$3,V5),"")
=IFNA(IFS($D$3=$S$3,T6,$D$3=$U$3,V6),"")
=IFNA(IFS($D$3=$S$3,T7,$D$3=$U$3,V7),"")
=IFNA(IFS($D$3=$S$3,T8,$D$3=$U$3,V8),"")
=IFNA(IFS($D$3=$S$3,T5,$D$3=$U$3,V5),"").... all the way to V65

 

[itsgrady]

=IFNA(IFS($D$3=$S$3,T5,$D$3=$U$3,V5),"") :Returns information in one column. The next day the other information goes in the column.

Returns a zero when empty need to remove the zero. The formula works well but for the zero when blank. If was able to remove zero for only day but not for the other day.

=IFNA(IF(T5="","",IFS($D$3=$S$3,T5,$D$3=$U$3,V5),"") )


The sheet has sixty rows with information in both S and U columns where the information stored for a daily form. The formula works well but it returns a zero when there is not information in a given cell in either column. The information goes in one column depending on the day it will pull information from one the columns in the calculation. I need different information each day and the two columns have different information but for the same form on a given day.

=IFNA(IFS($D$3=$S$3,T5,$D$3=$U$3,V5),"")
=IFNA(IFS($D$3=$S$3,T6,$D$3=$U$3,V6),"")
=IFNA(IFS($D$3=$S$3,T7,$D$3=$U$3,V7),"")
=IFNA(IFS($D$3=$S$3,T8,$D$3=$U$3,V8),"")
=IFNA(IFS($D$3=$S$3,T5,$D$3=$U$3,V5),"").... all the way to V65

I'm currently using the format cell to remove the zero - 0:-0;;@ - works well but it does limit me some. Thanks.

 

[Peter_SSs]

Could you give a small set of sample data with XL2BB and explain examples in relation to that sample data? I'm not quite sure which cells being empty are the problem. Is it $S$3 and/or $U$3 and/or T5 and/or V5 and/or $D$3?

 

[itsgrady]

Could you give a small set of sample data with XL2BB and explain examples in relation to that sample data? I'm not quite sure which cells being empty are the problem. Is it $S$3 and/or $U$3 and/or T5 and/or V5 and/or $D$3?

I have two columns with different names. I put the word "C Shift" in cell $D$3 and it pulls all the names from column "T" in the column "D" of the form I use. The next day I put the word "D Shift" in cell $D$3 and it pulls all the names from column "V" in the column "D" of the form I use. In all the cells of the D column I have the formula to read each row of column T or V. I am not not adding or dividing. When there is no data in the T or V column it returns a zero in the D column.

 

[Peter_SSs]

Thanks for the clarification. Try this formula.

=IF(AND(D$3=S$3,T5<>""),T5,IF(AND(D$3=U$3,V5<>""),V5,""))

 

[itsgrady]

Thanks for the clarification. Try this formula.

=IF(AND(D$3=S$3,T5<>""),T5,IF(AND(D$3=U$3,V5<>""),V5,""))

Thanks for the help.. So that I can understand a little better--what does T5<>"" do?

 

[itsgrady]

Thanks for the clarification. Try this formula.

=IF(AND(D$3=S$3,T5<>""),T5,IF(AND(D$3=U$3,V5<>""),V5,""))

Thank you very much... This code works very well and the "0" disappeared. I need to learn how to use the "and" function.

 

[Peter_SSs]

Thank you very much... This code works very well and the "0" disappeared.

You're welcome. Glad it worked for you. Thanks for the follow-up.

what does T5<>""

It checks that T5 is not empty. So T5 gets used only if D3=S3 AND T5 is not empty.