How to make cell Result as Blank, if one of sum of cells have no value

[pankajgrover]

How to make cell Result as Blank, if one of sum of cells have no value. In below example i want D3 and D4 should be result as blank until a3 and b4 have some values. Thanks

Shares Trading.xlsx
	A	B	C	D
1	Item	A	B	Total
2	pen	2500	3500	1000
3	pencil		5000	5000
4	rubber	4000		-4000
Sheet1

Cell Formulas
Range	Formula
D2:D4	D2	=C2-B2

 

[Sufiyan97]

Try in R10

=IFERROR(N(Q23)*IF(M23="",G23,M23)*N(B23)-N(O23),"")

 

[pankajgrover]

Try in R10

=IFERROR(N(Q23)*IF(M23="",G23,M23)*N(B23)-N(O23),"")

Finally perfectly working, Thanks

 

[Sufiyan97]

You're welcome.

 

[pankajgrover]

Hi @Sufiyan97 and @Peter_SSs
I need help in below formula. I want if O28 cell is blank then U28 cell should be blank, how to do this ?

Shares Trading.xlsx
	O			R	S	T	U
27	500.00			35850		1	35850
28				48000		1	48000
TEST

Cell Formulas
Range	Formula
O27:O28	O27	=IF(AND(N27<>"",H27<>""),C27*M27,0)
R27:R28	R27	=Q27*IF(M27="",G27,M27)*B27-O27
T27:T28	T27	=IF(E27="","",LEN(TRIM(E27))-LEN(SUBSTITUTE(E27," ",""))+1)
U27:U28	U27	=IF(ISNUMBER(SEARCH(U$2,$E27)),$R27/$T27,"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
U3:AC99	Cell Value	<0	text	NO
U3:AC99	Cell	does not contain a blank value	text	NO

 

[Sufiyan97]

Try

=IF(OR(ISBLANK(O28),O28=""),"",IF(ISNUMBER(SEARCH(U$2,$E28)),$R28/$T28,""))

 

[pankajgrover]

Try

=IF(OR(ISBLANK(O28),O28=""),"",IF(ISNUMBER(SEARCH(U$2,$E28)),$R28/$T28,""))

Thanks working now. Before i apply your formula not working, and after i look and i found that O column has values behinds 0 instead of blank so i edit your formula O28=0 instead of "" and its now working good. Thanks .

 

[pankajgrover]

Thanks working now. Before i apply your formula not working, and after i look and i found that O column has values behinds 0 instead of blank so i edit your formula O28=0 instead of "" and its now working good. Thanks .

hi @Sufiyan97 as per your formula which is proper working =IF(OR(ISBLANK(O28),O28=""),"",IF(ISNUMBER(SEARCH(U$2,$E28)),$R28/$T28,""))
and today i also tried this and also its working and same result =IF(O28="","",IF(ISNUMBER(SEARCH(U$2,$E28)),$R28/$T28,"")) without this (OR(ISBLANK(O28), is this necessary to include OR & ISBLANK ? any advantages for this or should i exclude this .. Thanks

 

[Sufiyan97]

Actually you can work without OR and ISBLANK

See for ISBLANK and "" results

Book1
	A	B	C	D
1		ISBLANK	""
2		FALSE	TRUE
3		TRUE	TRUE
4
5
Sheet1

Cell Formulas
Range	Formula
A2	A2	=""
B2:B3	B2	=ISBLANK(A2)
C2:C3	C2	=A2=""

So using just "" is fine, no need for ISBLANK.

 

[pankajgrover]

Actually you can work without OR and ISBLANK

See for ISBLANK and "" results

Book1
	A	B	C	D
1		ISBLANK	""
2		FALSE	TRUE
3		TRUE	TRUE
4
5
Sheet1

Cell Formulas
Range	Formula
A2	A2	=""
B2:B3	B2	=ISBLANK(A2)
C2:C3	C2	=A2=""

So using just "" is fine, no need for ISBLANK.

Thanks for example for easy understanding .