Ask Your Question

which function do I need?

asked 2020-01-06 09:02:30 +0100

ROSt52 gravatar image
  • 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.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2020-01-06 11:56:34 +0100

gregors15 gravatar image

updated 2020-01-06 12:12:08 +0100

Hi, please see attached. Let us know if it helpsC:\fakepath\LOQ_20200106.ods

edit flag offensive delete link more


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.

ROSt52 gravatar imageROSt52 ( 2020-01-06 12:47:02 +0100 )edit

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?

gregors15 gravatar imagegregors15 ( 2020-01-06 13:01:31 +0100 )edit

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

Opaque gravatar imageOpaque ( 2020-01-06 13:09:36 +0100 )edit

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?

ROSt52 gravatar imageROSt52 ( 2020-01-06 15:04:55 +0100 )edit

How can I upload a file here?

ROSt52 gravatar imageROSt52 ( 2020-01-06 15:05:57 +0100 )edit

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 gravatar imageerAck ( 2020-01-06 16:11:34 +0100 )edit

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.

erAck gravatar imageerAck ( 2020-01-06 16:21:06 +0100 )edit

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.)

ROSt52 gravatar imageROSt52 ( 2020-01-07 01:22:59 +0100 )edit

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.

erAck gravatar imageerAck ( 2020-01-07 19:35:51 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-01-06 09:02:30 +0100

Seen: 99 times

Last updated: Jan 06 '20