SUMPRODUCT with several criteria

Hi all,

How can I rewrite below formula to only take into account the cells that contain a number that is greater than zero, as well as all cells that contain an "x" (such as "x", " x", "xx" or "xxx" etc.), but excluding cells that contain e.g. "LUX" which obviously has an "x" in it but rather forms a word...

Please Login or Register  to view this content.

current formula returns a #VALUE error.

If you want to count the number of times a character is repeated in a string:
=LEN(A1)-LEN(SUBSTITUTE(A1,"x",""))
This is better than the COUNT(SEARCH()) combination you used as it returns the number of times "x" appears in the string.

Now, if you expand this to =IF(LEN(A1)-LEN(SUBSTITUTE(A1,"x",""))>=LEN(A1),1,0) you add a test to see if there are characters OTHER THAN X in the string. If so, as in the case of "LUX", it will return 0.

Note, this will only work where there is x, xx, xxx etc. If there are spaces anywhere, the test will not work.

I hope this helps, please let me know!

Regards,

David

If this has been helpful
- Please click on the *Add Reputation button at the bottom left.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Originally Posted by David A Coop

If you want to count the number of times a character is repeated in a string:
=LEN(A1)-LEN(SUBSTITUTE(A1,"x",""))
This is better than the COUNT(SEARCH()) combination you used as it returns the number of times "x" appears in the string.

Now, if you expand this to =IF(LEN(A1)-LEN(SUBSTITUTE(A1,"x",""))>=LEN(A1),1,0) you add a test to see if there are characters OTHER THAN X in the string. If so, as in the case of "LUX", it will return 0.

Note, this will only work where there is x, xx, xxx etc. If there are spaces anywhere, the test will not work.

I hope this helps, please let me know!

Regards,

David

hmm... I think the "LUX" case makes it unnecessarily complex. How about if it is supposed to take all cells into account that contain an "x" no matter if it is a word such as "LUX" or if there are spaces in between and all cells that contain POSITIVE numbers so excluding everything below zero and zero itself?

Originally Posted by nflsales

try

=SUMPRODUCT(--(ISNUMBER(1/$EG13:$FI13)+ISNUMBER(SEARCH($EG13:$FI13&"",REPT("X",10)))>0),--($EG13:$FI13<>""),$EG$9:$FI$9,$EG$3:$FI$3)

It DOES count negative numbers (even though it shouldn't) and DOES NOT count "x"s with a space somewhere (even though it should)

Originally Posted by esbencito

It DOES count negative numbers (even though it shouldn't) and DOES NOT count "x"s with a space somewhere (even though it should)

Just realised that it does NOT count words with "x" which is awesome! just negative numbers need to be removed (including 0) and "x"s with a space somewhere INCLUDED... is that feasible? Or does the space/s make it too difficult?

Impressive!! That works just fine!

So one last thing... how would I apply the same to an IF(OR formula rather than a SUMPRODUCT? I have a combination of both, but it does not seem to work for the example below.

Please Login or Register  to view this content.

it basically runs a different calculation depending on if one of the criteria is met. If the cell EG13 contains e.g. "x", " x", "xx", "xxx", "5", "10" etc. it runs the SUMPRODUCT formula, if not, such as "Lux", "-2", "-8", "Fox" etc. it SUMs a different cell range.

Yup, that works! Just for my learning, could you explain what

Please Login or Register  to view this content.

does exactly?

Originally Posted by JeteMc

It checks to see if the value in EG13 is numeric returning either true or false. The formula then goes on to see if the value in EG13 might be x or a grouping of x's and adds those results together using Boolean logic (T + T = T, F + F = F, T + F = T) The final part of the first sumproduct checks to see if the value of EG13 is greater than zero (alpha values will be). This part is multiplied using boolean logic (T * T = T, F * F = F, T * F = F) I hope this makes sense.
A good way to see what the formula is doing is to use the Evaluate Formula feature (on the Formulas tab in the 2010 version).

Thanks for clarifying the Boolean logic, highly appreciated! Always good to learn new stuff

I used that particular fragment because it had been used in post #8 so I thought there might provide continuity with what was already known. Doing a bit of testing it seems that ISNUMBER(EG13) would yield the same results as would ISNUMBER(EG13:FI13) used instead of ISNUMBER(1/EG13:FI13).
I believe that you'll get the same overall results with:

Formula:

Please Login or Register  to view this content.

I also tried the formula without the

Please Login or Register  to view this content.

and simply used

Please Login or Register  to view this content.

instead, and it seems to indeed return the same results. Just wanted to check with you if there's anything I was missing if I remove it!