How to count Cells with Text if Text is created by Formula?

[Pantona]

Assumed we have the following usecase:

• In sheet 1 column A there are a few cells with words in it (e.g. “Holiday”)

  Column A
  1 Holiday
  2
  3 Holiday
  4 Holiday

• In sheet 2 column A all cells simply contain a formula that copies this content from sheet 1 to sheet 2 (=Sheet_1.A1)

  Column A

  1 =Sheet_1.A1
  2 =Sheet_1.A2
  3 =Sheet_1.A3
  4 =Sheet_1.A4

Now, within sheet 2, I want to count all the cells that contain a word. Usually this is possible with

=COUNTIF(A:A;"[:alpha:]*")

However, since I have formulas (instead of real text) in those cells, this function does not work. Any ideas how to solve?

[mikekaganski]

@Pantona

I want to count all the cells that contain a word. Usually this is possible with

=COUNTIF(A:A;"[:alpha:]*")

@karolus

Anyway, why not directly:

=COUNTIF(Sheet_1.A:A;"[:alpha:]+")

@Pantona

Because in reality …

Note that the important difference was in * vs. + in the regex. The idea that you can count “cells that contain a word” using a regex that matches zero or more characters is wrong

[PKG]

Works without using regular expressions

=COUNTIF(Sheet_1.A:A;">")

[Pantona]

@PKG

Works without using regular expressions

=COUNTIF(Sheet_1.A:A;">")

Yeah indeed! Thank you so much!

[karolus]

guess you need to enable: →Tools→Options→LO-Calc→Calculate →→→allow regular Expressions in Formulas

Anyway, why not directly:

=COUNTIF(Sheet_1.A:A;"[:alpha:]+")

[Pantona]

Thank you very much for your kind help!

@karolus

guess you need to enable: →Tools→Options→LO-Calc→Calculate →→→allow regular Expressions in Formulas

Just for better understanding: What does this cause? Unfortunately I couldn’t find this function in the mentioned settings…

@karolus

Anyway, why not directly:

=COUNTIF(Sheet_1.A:A;"[:alpha:]+")

Because in reality sheet 1 is sheet 2 and sheet 2 is sheet 3. So even the cells in sheet 1 (= 2) are created by some fancy formula input coming from sheet 1, so there is also no “real” text available. Additionally, I am forced to copy the cell content from sheet 2 instead of the origin, because sheet 1 has a different structure (different day/holiday-relations).

@karolus

your assumption is wrong, every Function works on the output of Formulas and not on the Formulas, …… except one Function =FORMULA(…) which returns litterally the Formula

This is good to know! But then even more I am wondering why my formula does not detect the cell content as text…

[karolus]

@Pantona

However, since I have formulas (instead of real text) in those cells, this function does not work.

your assumption is wrong, every Function works on the output of Formulas and not on the Formulas, …… except one Function =FORMULA(…) which returns litterally the Formula