Column Sum problem

[Pacman52]

I have a column on a worksheet (Col P) where I need to to sum any values entered consecutively in row P3 to P505, so in row P507 I simply entered
'=Sum(P3:P505)'. But even though it didn't show any formula errors it also didn't show a total number either.

I double checked all the rows values to make sure they were just numbers (which thy were) and re typed the sum formula but again it didn't work.
I then went through the process of manually adding ONLY the rows that had values in them using P3+P4+P5+.. up to P27 and this worked, the total showed in P507.

Could anyone let me know why the sum formula didn't work and what I could check or change to fix this.

Thanks Paul

 

[Peter_SSs]

'=Sum(P3:P505)'. But even though it didn't show any formula errors it also didn't show a total number either.

What did it show?

 

[Pacman52]

sorry I should of said that ! So when I first pressed enter after typing the formula, it looked like the cell (P507) had accepted it as I saw it 'activate' but after that it didn't show anything in the cell but the formula bar showed the formula.

 

[Peter_SSs]

sorry I should of said that ! So when I first pressed enter after typing the formula, it looked like the cell (P507) had accepted it as I saw it 'activate' but after that it didn't show anything in the cell but the formula bar showed the formula.

That sounds to me like cell P507 is formatted so that the font colour is the same as the cell background colour.
What happens if you select P507 and format the font colour to red or blue for example?

 

[Pacman52]

Hi Peter, thanks for the reply again,

There is some conditional formatting on P3:P503 but P507 is clear of any formatting apart from currency, the cell background is dark blue with the font being white and bolded and after double checking this is all as it should be.

The only 'odd thing' is the Sum formula works on other columns on the same sheet, so I am at a lost as to why its not working on Col P.
Its not really a major problem to be honest, as I mentioned before manually calculating using '+' works fine and ends up with the same result so other cell can do their calculations.

It may be prudent to mention P3:P503 are referencing another worksheet that is getting its data from a Userform, although again all the other columns on the problematic worksheet also follow the same pattern and they work fine using Sum.

 

[Peter_SSs]

Are you able to upload a copy of the workbook (with any sensitive information disguised or removed) showing the issue to DropBox or One Drive or Google drive etc and post a public link here so we could take a look and see if we get the same behaviour from the file?

 

[Pacman52]

Hi Peter it would be easy to remove any sensitive stuff from the worksheets and upload, but the workbook is so 'complicated' in what has been designed with code and Userforms, with sheets referencing other sheets ect and not to mention password protection, I really don't think it would be a good use of your time or mine to be honest although I really do appreciate your kind offer to have a look at it for me.

As said I do have a workable solution by manually adding the column using '+' and in reality I've only had to do it once as I used 'find and replace' to rename the columns letters needed.

When I do get a bit of time after I've finished this project I do plan to look at it again though as its just bugging me why it wouldn't work. If I do find out what was causing it, I will post back on this thread though.

Thanks again for your replies. Paul

 

[Peter_SSs]

When I do get a bit of time after I've finished this project I do plan to look at it again though as its just bugging me why it wouldn't work. If I do find out what was causing it, I will post back on this thread though.

Yes, it is intriguing me too which is why I was keen to see it. I would be interested to know if you find out, or see the file, at some time.