Error using Count function

[Sean89]

Hi all,

I am trying to fix a broken formula.
I have attached a photo.

Using count function i am trying to count all number cells in the range. This works fine except for when the cell has a space or - or / or : basically if i use anything in the cell other than numbers.

The aim is to use the sheet as a rota. Im trying to count the amount of staff that are on shift. 99% of the staff work set shift patterns so they know if they start at 8.15 then their finish time will be 6 hence why there are no finish times. However there are a couple of staff that do smaller and or random shifts and they require a start and finish time. How can i get the cell to count all of the numbered cells???

Thanks in advance

[FJCC]

COUNT() is not counting cells with - and / in them because it is counting cells that contain pure numbers and not text. Cell content like 8.15 - 6.15 is text. A formula that will count the number of cells that contain a numeric character is

Code: Select all

=COUNTIF(L9:L19;"\d")

For that to work, you have to go to the menu Tools -> Options -> OpenOffice Calc -> Calculate and:
Turn off Search criteria = and <> must apply to whole cells
Turn on Enable regular expression in formulas

[RusselB]

If you want to count anything that is entered in the cell, then the COUNTA function looks like an option.
No need to change the settings required for FJCC's suggestion.

[Zizi64]

I have attached a photo.

It is better to attach a Print Screen image. That will not be blurry.

The best is to upload a real ODF type sample file as an attachment.