Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenSat, 05 Jan 2019 11:06:27 +0100Exclude strings in parentheses with SUMPRODUCThttps://ask.libreoffice.org/en/question/178284/exclude-strings-in-parentheses-with-sumproduct/Hello!
I have the following formula : =SUMPRODUCT(AA19:BX19<>"",AA19:BX19<>"X",AA19:BX19<>G19,AA19:BX19<>L19,AA19:BX19<>P19).
I need this formula to count how many cells contain none of the values (strings) "", "X" and none of the values (strings) of the cells G19, L19, and P19. I need to modify this formula in order to exclude all the strings (again in the same range AA19:BX19) that are included in parentheses, so they are not going to be counted. For example I need to exclude anything like (JOHN), (MARY), (TOM), (GEORGE). How can I do this? Thanks!
Fri, 04 Jan 2019 22:19:52 +0100https://ask.libreoffice.org/en/question/178284/exclude-strings-in-parentheses-with-sumproduct/Comment by alex72gr for <p>Hello!
I have the following formula : =SUMPRODUCT(AA19:BX19<>"",AA19:BX19<>"X",AA19:BX19<>G19,AA19:BX19<>L19,AA19:BX19<>P19).
I need this formula to count how many cells contain none of the values (strings) "", "X" and none of the values (strings) of the cells G19, L19, and P19. I need to modify this formula in order to exclude all the strings (again in the same range AA19:BX19) that are included in parentheses, so they are not going to be counted. For example I need to exclude anything like (JOHN), (MARY), (TOM), (GEORGE). How can I do this? Thanks!</p>
https://ask.libreoffice.org/en/question/178284/exclude-strings-in-parentheses-with-sumproduct/?comment=178298#post-id-178298I thought of a solution... =SUMPRODUCT(AA19:BX19<>"",AA19:BX19<>"X",AA19:BX19<>G19,AA19:BX19<>L19,AA19:BX19<>P19)-COUNTIF(AA19:BX19,"(*)")
This formula seems to result in what I need... But... is there any other more simple way? I mean... by using just the SUMPRODUCT function? Thanks again!Sat, 05 Jan 2019 01:06:15 +0100https://ask.libreoffice.org/en/question/178284/exclude-strings-in-parentheses-with-sumproduct/?comment=178298#post-id-178298Comment by Mike Kaganski for <p>Hello!
I have the following formula : =SUMPRODUCT(AA19:BX19<>"",AA19:BX19<>"X",AA19:BX19<>G19,AA19:BX19<>L19,AA19:BX19<>P19).
I need this formula to count how many cells contain none of the values (strings) "", "X" and none of the values (strings) of the cells G19, L19, and P19. I need to modify this formula in order to exclude all the strings (again in the same range AA19:BX19) that are included in parentheses, so they are not going to be counted. For example I need to exclude anything like (JOHN), (MARY), (TOM), (GEORGE). How can I do this? Thanks!</p>
https://ask.libreoffice.org/en/question/178284/exclude-strings-in-parentheses-with-sumproduct/?comment=178303#post-id-178303You could use the new [REGEX function](https://help.libreoffice.org/6.2/en-US/text/scalc/01/func_regex.html) available in upcoming [6.2](https://wiki.documentfoundation.org/ReleaseNotes/6.2#New_spreadsheet_functions) :-) - then it would look like `=SUMPRODUCT(AA19:BX19<>"";AA19:BX19<>"X";AA19:BX19<>G19;AA19:BX19<>L19;AA19:BX19<>P19;ISERROR(REGEX(AA19:BX19;"^\(.*\)$")))`Sat, 05 Jan 2019 02:02:56 +0100https://ask.libreoffice.org/en/question/178284/exclude-strings-in-parentheses-with-sumproduct/?comment=178303#post-id-178303Comment by alex72gr for <p>Hello!
I have the following formula : =SUMPRODUCT(AA19:BX19<>"",AA19:BX19<>"X",AA19:BX19<>G19,AA19:BX19<>L19,AA19:BX19<>P19).
I need this formula to count how many cells contain none of the values (strings) "", "X" and none of the values (strings) of the cells G19, L19, and P19. I need to modify this formula in order to exclude all the strings (again in the same range AA19:BX19) that are included in parentheses, so they are not going to be counted. For example I need to exclude anything like (JOHN), (MARY), (TOM), (GEORGE). How can I do this? Thanks!</p>
https://ask.libreoffice.org/en/question/178284/exclude-strings-in-parentheses-with-sumproduct/?comment=178320#post-id-178320Thank you! :) I think that I should wait for the release of v6.2. Up until then, I'll use my formula.Sat, 05 Jan 2019 11:06:27 +0100https://ask.libreoffice.org/en/question/178284/exclude-strings-in-parentheses-with-sumproduct/?comment=178320#post-id-178320