Why I have I lost the function and only get the value after I save and reopen the spreadsheet?

Question

I am using desktop Excel on Windows.

I have a single spreadsheet. In one cell, I have written a formula =countif(a1:a10, "*1"). I get a correct value.

My question is, why have I lost the function and get only the value after I save and reopen the spreadsheet?

Did you save as the Excel format, or some other format that is not capable of keeping formula (e.g. CSV, TXT etc)? — Kenneth L, May 3, 2018 at 2:17
what do you mean by lost the formula & only get value? Did the formula disappears ? rewrite your formula, since it's preceded with ' sign which convert it into Text ans also the wild card "*1" is wrong !! — Rajesh Sinha, May 3, 2018 at 4:12
@n179911, could you please write what answer your formula '=counif(a1:a10, "*1")" has given & what you are trying to achieve, please upload the screen shot also !! — Rajesh Sinha, May 3, 2018 at 11:43

Rajesh Sinha · Accepted Answer · 2018-05-03 05:27:05Z

-3

The formula you have used '=counif(a1:a10, "*1")"

should to be written like, =counif(a1:a10, "*1").

And it suggests that, you are trying to count all number which ends with 1 . In this case you need to use the following array formula.

{=IF(RIGHT(A1:A5,1)="1",COUNT(A1:A5),"")}

Note:, Finish this formula with CRTL+SHIFT+ENTER, & adjust the data range as you need.

answered May 3, 2018 at 5:21

Rajesh Sinha

9,2246 gold badges17 silver badges37 bronze badges

You don't answer the question (My question is, why have I lost the function and get only the value after I save and reopen the spreadsheet?) but suggest improvements where OP's formula works as expected.
– Máté Juhász
May 3, 2018 at 8:39
@MátéJuhász, at the beginning of my answer I've mentioned that what basic mistake OP has commited and wrote the formula like '=counif(a1:a10, "*1")" and this was the reason Excel considers it asText rather than Formula. I'm sure OP misunderstood & written that, " Why I've lost my Formula" !!
– Rajesh Sinha
May 3, 2018 at 11:36
OP clearly wrote that the formula had worked correctly before reopening the workbook, "s just remained in the question during edits.
– Máté Juhász
May 3, 2018 at 11:38
I've edited the question just now, is it more clear now?
– Máté Juhász
May 3, 2018 at 11:39
@MátéJuhász,, do you also think that, '=counif(a1:a10, "*1")" really works ??
– Rajesh Sinha
May 3, 2018 at 11:40

| Show 3 more comments

1 Answer 1