=SUM(C18:C20) Calculating wrong missing out cell

=SUM(C18:C20)

C18=30
C19=40
C20=20

Obviously should add up to 90 but is saying 60

If i putt it as =SUM(C19:C20)+C18 it does calculate 90.

Its happening for D18,E18 and so on

The cell C18 is formula

=IF(NOT(ISBLANK('16th'!F18)), '16th'!F18, "")

but C19 is

=IF(NOT(ISBLANK('17th'!F18)), '17th'!F18, "")

same really

Have tried formatting all different cells every way and it just refuses to work.
Any suggestions?

What do you get from

=isnumber('16th'!F18)

My guess would be that it's going to return false, meaning the source data contains numbers formatted as text.

You get better help if you post an Excel-example of your workbook, without confidential information.

=isnumber('16th'!F18) does return false

i suspected that i just cant find the fix

I have formatted the cells again to general and that doent work

I have tried uploading workbook but it is too big

Have cell 18 force its input into a number.

Please Login or Register  to view this content.

That should fix it.

Yes ben

Top man

Much appreciated the end of this workbook is now in sight

If numbers are entered in text format then just changing the cell format doesn't fix it, the values have to be re-entered, or you at least have to make excel think that they have.

One way is with a simple code loop, make sure that you have the offending '16th' sheet selected before you continue.

Press Alt + F11 to open the code editor, then select Insert > Module from the editor menu.

Copy the code below and paste it into the editor, then press f5 to run the code.

Please Login or Register  to view this content.