Which function do I need?

[ROSt53]

• I have a colum of cells
• each cell contains a formular
• formular displays under certain conditions an email address (this if-function works correctly)
• at the bottom of the column, I need to count the number of email addresses in this very column.

What kind of function do I need to count the email addresses being calculated by an if-formular?

As an identifier for an email address, the “@” can be used; it is not the result of any other if-function result.

Thanks a lot in advance for your help.

[gregors15]

Hi, please see attached. Let us know if it helpsLOQ_20200106.ods

[ROSt53]

Thanks for attempt.
Formular in attached sheet doesn’ t work, Also in attached spreadsheet counting does not work

I tried also =COUNTIF(FV3:FV25,“asterix@asterix”) which does it neither.

“asterix” is the normal asterix symbol which cannot be used here.

[gregors15]

Hi, in the sheet I uploaded the COUNTIF returned a count of 22 that contained @. Are you able to upload a sample of your file so we can have a look?

[anon73440385]

@ROSt53 - for me both formulas from the attached file work even if create the email addresses using an IF() formula.

[ROSt53]

Thanks, now I wonder where the problem is.
When I open the attached file, it opens in edit mode and shows 0 and not the 22. The same when I save an open,
I remember that I, several month ago used the second formula and it worked. Revisiting my file again to complete the work made me wondering why the formula did not work.
What could the reason? A cell format? What else?

[ROSt53]

How can I upload a file here?

[erAck]

For =COUNTIF(FV3:FV25,"*@*") to work, wildcards must be enabled under Tools → Options → Calc → Calculate, Enable wildcards in formulas.

The more sophisticated regular expressions enabled would need a different formula expression, i.e. =COUNTIF(FV3:FV25,".*@.*") or rather =COUNTIF(FV3:FV25,".+@.+") as both left and right of the @ there must be some characters.

Note that wildcards and regular expressions are mutually exclusive, only one can be active throughout the entire document.

[erAck]

If the attached sample document that uses wildcards doesn’t work for you it may indicate that you are using a very old LibreOffice version where simple wildcards were not implemented yet.

[ROSt53]

Thanks for the hint with the wildcards, They were not enabled. Both formulas work!

I’m using LibO 5.4 and once my PC is migrated to LM 19.3 I will get the latest version of LibO installed as well.

Before I close this question, please advice how I can credit you and the others who helped? (Sorry, since I worked in the forum intensively many things have changed, thus I appreciate your advice.)

[erAck]

Click the check mark on an answer to mark the answer as correct, and click the up arrow that appears if you hover over a helpful comment.