Ask Your Question

'formula shows' in calc unwanted

asked 2018-09-17 20:54:02 +0100

ttweed gravatar image

I don't have the 'show formula' option selected (Mac OS X.9.5), but instead of calculating, it simply displays in the cell. I am a neophyte, so perhaps my formula is bad? It is: =COUNTIF(D1:D1592,"Syn") ... and my intent is to count the number of times in that long column that the text string 'Syn' occurs.

At bottom of another column another formula, =COUNTA(E1:E1592), to count all cells with nothing in them, calculates, but returns 557, when the total should be around 1,000...

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2018-09-18 16:01:37 +0100

ttweed gravatar image

Vielen Dank, erAck. But no progress, other than it returns '0' (the correct ~200 count). Whenever I change the number format to 'All / General' it seems to automatically revert to 'Number / General' format. I plan to do no arithmetic with the cell contents of this spreadsheet, just aggregating their text content, as per the two formulas I am trying. Strange that one formula possibly is partially counting, the other one is accepted but returns a count of zero... Ideas, anyone?

edit flag offensive delete link more


Please use add a comment instead of Answer it it doesn't answer the original question.

However, changing the number format does not change the cell content, if it was text it stays text. So once the number format of the cell was changed to General you'll have to re-enter the formula (just place the cell in edit mode with F2, then append and remove a blank to have it modified, and then press Enter).

erAck gravatar imageerAck ( 2018-09-18 16:41:14 +0100 )edit

For the =COUNTA(E1:E1592) that does count cells with content, not without (as you wrote "to count all cells with nothing in them").

erAck gravatar imageerAck ( 2018-09-18 16:42:12 +0100 )edit

answered 2018-09-17 21:44:09 +0100

erAck gravatar image

Make sure the cell hasn't applied a number format Text (@) before entering a formula or number, else all input will result in literal text instead of a formula or numeric value.

See also this FAQ.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-09-17 20:54:02 +0100

Seen: 33 times

Last updated: Sep 18 '18