#VALUE! error while using sum two cells having VLOOKUP

[mwvirk]

how i can resolve this #value! error since i have 2 different formulas in cell BR & BS:

MWVirk PSX Portfolio v0004.xlsx
	BR	BS	BT
32	13,914.94	11,900.00	25,814.94
33	4,964.18	2,992.00	7,956.18
34			#VALUE!
35			#VALUE!
36			#VALUE!
37			#VALUE!
38			#VALUE!
39			#VALUE!
40			#VALUE!
41			#VALUE!
42			#VALUE!
43			#VALUE!
44			#VALUE!
45			#VALUE!
46			#VALUE!
47	-578,863.36	0.00	-578,863.36
48	3,110.30	0.00	3,110.30
PSX Profit & Loss

Cell Formulas
Range	Formula
BR32:BR48	BR32	=IF(ISNA(VLOOKUP(A32,'PSX Portfolio Status'!A:BN,31,FALSE)),"",VLOOKUP(A32,'PSX Portfolio Status'!A:BN,31,FALSE))
BS32:BS48	BS32	=IF(ISNA(VLOOKUP(A32,'PSX Portfolio Status'!A:BN,54,FALSE)),"",VLOOKUP(A32,'PSX Portfolio Status'!A:BN,54,FALSE))
BT32:BT48	BT32	=BR32+BS32

Named Ranges
Name	Refers To	Cells
'PSX Portfolio Status'!_FilterDatabase	='PSX Portfolio Status'!$A$1:$BN$1	BR32:BS48

 

[Alex Blakenburg]

Just change your formula to use Sum.
(Sum ignores "" or treats is as 0, while +/- does not)

=SUM(BR32:BS32)

Since your 2 references are contiguous, stick with the above but if they weren't using a comma would let you add multiple non-contiguous cells without the VALUE issue.

=SUM(BS32, BT32)

 

[Peter_SSs]

BTW, you can write your column BR & BS formulas more simply. For example, instead of

=IF(ISNA(VLOOKUP(A32,'PSX Portfolio Status'!A:BN,31,FALSE)),"",VLOOKUP(A32,'PSX Portfolio Status'!A:BN,31,FALSE))

you can use

=IFNA(VLOOKUP(A32,'PSX Portfolio Status'!A:BN,31,FALSE),"")

 

[mwvirk]

Just change your formula to use Sum.
(Sum ignores "" or treats is as 0, while +/- does not)

=SUM(BR32:BS32)

Since your 2 references are contiguous, stick with the above but if they weren't using a comma would let you add multiple non-contiguous cells without the VALUE issue.

=SUM(BS32, BT32)

used Sum(BR32:BS32) and it's ok.
just a small concern. is it possible to avoid 0.00

 

[Joe4]

Have a look at the "Hide zero values in selected cells" section here: Display or hide zero values - Microsoft Support

 

[mwvirk]

Have a look at the "Hide zero values in selected cells" section here: Display or hide zero values - Microsoft Support

thank you but i don't want to hide the 0 value for the entire sheet

 

[Alex Blakenburg]

Try:

=IF(AND(BR32="",BS32=""),"",SUM(BR32:BS32))

 

[mwvirk]

Try:

=IF(AND(BR32="",BS32=""),"",SUM(BR32:BS32))

perfect

thank you so much

 

[Alex Blakenburg]

You're welcome. Glad we could help.

 

[Joe4]

thank you but i don't want to hide the 0 value for the entire sheet

Note that you can apply that formatting to just the range you want, you do not need to do it for the entire sheet!