Exclude strings in parentheses with SUMPRODUCT

asked 2019-01-04 22:19:52 +0100

alex72gr gravatar image

updated 2020-07-29 12:00:20 +0100

Alex Kemp gravatar image

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!

edit retag flag offensive close merge delete


I 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!

alex72gr gravatar imagealex72gr ( 2019-01-05 01:06:15 +0100 )edit

You could use the new REGEX function available in upcoming 6.2 :-) - then it would look like =SUMPRODUCT(AA19:BX19<>"";AA19:BX19<>"X";AA19:BX19<>G19;AA19:BX19<>L19;AA19:BX19<>P19;ISERROR(REGEX(AA19:BX19;"^\(.*\)$")))

Mike Kaganski gravatar imageMike Kaganski ( 2019-01-05 02:02:56 +0100 )edit

Thank you! :) I think that I should wait for the release of v6.2. Up until then, I'll use my formula.

alex72gr gravatar imagealex72gr ( 2019-01-05 11:06:27 +0100 )edit