Sum function returning 0

[EBoudreau]

I have a spread sheet where I'm trying to get a calculation of a sum of two cells where their data is referenced from two other sheets. And I'm getting 0 as a result.

What I'm trying to do is:
in cell R10, I want R10 to =SUM(O10:O11). It should =80 but it's not. Formatting is the same on all the cells.

Those two cells are referencing two other sheets. Not sure if that matters.
Cell O10 ='Week (1)'!I22
Cell O11 ='Week (2)'!I22

I may need a little guidance as to why that is not calculating properly.

Timecard (sum error).xlsx
	O	P	Q	R	S
10	40.00	40.00	$0.00	0.00	80.00
11	40.00	40.00
12	40.00	40.00	$0.00	0.00	80.00
13	40.00	40.00
14	40.00	40.00	$0.00	0.00	80.00
15	40.00	40.00
16	0.00	0.00	$0.00	0.00	0.00
17	0.00	0.00
18	0.00	0.00	$0.00	0.00	0.00
19	0.00	0.00
Schedule - Summary

Cell Formulas
Range	Formula
O10	O10	='Week (1)'!I22
P10	P10	='Week (1)'!I23
Q18:S18,Q16:S16,Q14:S14,Q12:S12,Q10:S10	Q10	=SUM(N10:N11)
O11	O11	='Week (2)'!I22
P11	P11	='Week (2)'!I23
O12	O12	='Week (3)'!I22
P12	P12	='Week (3)'!I23
O13	O13	='Week (4)'!$I$22
P13	P13	='Week (4)'!I23
O14	O14	='Week (5)'!$I$22
P14	P14	='Week (5)'!I23
O15	O15	='Week (6)'!$I$22
P15	P15	='Week (6)'!I23
O16	O16	='Week (7)'!$I$22
P16	P16	='Week (7)'!I23
O17	O17	='Week (8)'!$I$22
P17	P17	='Week (8)'!I23
O18	O18	='Week (9)'!$I$22
P18	P18	='Week (9)'!I23
O19	O19	='Week (10)'!$I$22
P19	P19	='Week (10)'!I23

 

[Scott T]

Are you sure the values in the cells are values and not text? If you do a ISNUMBER the cells does it return true (it is a number) or False (it is not a number)?

If it is text doing a math function will turn it into a value so you can +0 to it and get a number.

 

[EBoudreau]

Are you sure the values in the cells are values and not text? If you do a ISNUMBER the cells does it return true (it is a number) or False (it is not a number)?

If it is text doing a math function will turn it into a value so you can +0 to it and get a number.

Would I verify that by formatting the cells and Number 0.00 for example? How would I do a ISNUMBER?

UPDATE: I did just do the ISNUMBER and the two cells I'm trying to sum together are false. How do I convert them to a number?

UPDATE: I got it figured. Turning Cell O10 ='Week (1)'!I22+0 made it a number.
THank you very much for the insight.

 

[Scott T]

To check both of the you use these 2 formulas.

=ISNUMBER(O10) 
=ISNUMBER(O11)

 

[EBoudreau]

To check both of the you use these 2 formulas.

=ISNUMBER(O10)
=ISNUMBER(O11)

Thank you very much for the assistance. That cleared it all up for me. I went through the rest of my spreadsheet and found all sorts of cells further down that same column and the OT column as well that were FALSE. Makes sense why my spreadsheet wasn't working very well at all!

 

[jasonb75]

You shouldn't need to use extra cells to add 0 in order to convert them to proper numbers.

Type 0 into any empty cell, then copy that cell and pastespecial > add to the cells that need to be converted.

 

[StephenCrump]

... or you can select the offending cells, and key ALT+D,E,F.

 

[arthurbr]

When building your sheet you should, at first, NEVER manually align column contents. A numbers are right-aligned, and text left-aligned, you can spot the problem immediately.