Problem with the Sum function

[gsdanger]

Good Day all,

I hope someone can assist me with this problem.

In Excel 2019, I have 7 consecutive cells (EG3 through to EM3).

There will be only 1 number in each row (Sat to Fri).

EG3​	EH3​	EI3​	EJ3​	EK3​	EL3​	EM3​	EN3​
Sat​	Sun​	Mon​	Tue​	Wed​	Thu​	Fri​	Sum​
	​	​	​	​	5​	​	0​
	​	4​	​	​	​	​	0​

​

These numbers are the result of a Vlookup table looking at the week day and assigning the value into the appropriate cell, as per the example above.

I have a sum function in EN3 (=SUM(EG3:EM3). Should give me the totals of 5 and 4 in the respective EN3 and EN4 cells. However, I am getting a zero (0) for the sum..??

The source of the lookup table is formatted to General, as are all the above cells, and is functioning correctly (ie: it assigns the correct number to the correct cell).
If I actually type in the 5 and 4 numbers, in lieu of the vlookup formula, the sum returns the correct totals (IE: 5 and 4).

How can I get this sum function to return the correct totals?


Please assist.

Kind Regards.

Gsdanger.

 

[arthurbr]

Be aware formatting is only the way XL shows you things, not the underlying value
There is a good chance that the numbers are actually text looking like numbers, this is hidden by the fact that you manually centered the cells. If when removing this alignment the values align left, you have text.
One way would be to add 0 to your VLOOKUP, like =0+VLOOKUP... as arithmetic operations coerce text to numbers .

 

[Dave Patton]

In addition to arthurbr's comments.
Review the Vlookup table; can you edit it to yield a number? Probably the best solution.

The range in the example below is not the same as your example.
The formula will sum the text values.
N.B. It includes 2 rows.

T202106a.xlsm
	G	H	I	J	K	L	M	N
2	Sat	Sun	Mon	Tues	Wed	Thu	Fri
3						5
4			4					9
1a

Cell Formulas
Range	Formula
N4	N4	=SUMPRODUCT(--(G3:M4))

 

[gsdanger]

In addition to arthurbr's comments.
Review the Vlookup table; can you edit it to yield a number? Probably the best solution.

The range in the example below is not the same as your example.
The formula will sum the text values.
N.B. It includes 2 rows.

T202106a.xlsm
	G	H	I	J	K	L	M	N
2	Sat	Sun	Mon	Tues	Wed	Thu	Fri
3						5
4			4					9
1a

Cell Formulas
Range	Formula
N4	N4	=SUMPRODUCT(--(G3:M4))

Good Day all,

I hope someone can assist me with this problem.

In Excel 2019, I have 7 consecutive cells (EG3 through to EM3).

There will be only 1 number in each row (Sat to Fri).

EG3​	EH3​	EI3​	EJ3​	EK3​	EL3​	EM3​	EN3​
Sat​	Sun​	Mon​	Tue​	Wed​	Thu​	Fri​	Sum​
	​	​	​	​	5​	​	0​
	​	4​	​	​	​	​	0​

​

These numbers are the result of a Vlookup table looking at the week day and assigning the value into the appropriate cell, as per the example above.

I have a sum function in EN3 (=SUM(EG3:EM3). Should give me the totals of 5 and 4 in the respective EN3 and EN4 cells. However, I am getting a zero (0) for the sum..??

The source of the lookup table is formatted to General, as are all the above cells, and is functioning correctly (ie: it assigns the correct number to the correct cell).
If I actually type in the 5 and 4 numbers, in lieu of the vlookup formula, the sum returns the correct totals (IE: 5 and 4).

How can I get this sum function to return the correct totals?


Please assist.

Kind Regards.

Gsdanger.

Attention arthurbr and Dave Patton,
Thank you for being so prompt in responding to my summing problem.
arthurbr, I modified my vlookup formula to incorporate the additions you suggested, and just like magic...the sum function now works.
Thank you very much, and also to Dave Patton for your input.
Kind Regards....to both of your...Thanks to both of you.