Counting excluding a string

[abhi1693r]

Hi,

 I am counting 2 columns A and B, Count( DISTINCT A&'|'&B)

I want to exclude any values in column C with the prefix "ROM".

tried using 

=Count( DISTINCT {<C-={'ROM'}>} A&'|'&B)

Both Count( DISTINCT A&'|'&B) and Count( DISTINCT {<C-={'ROM'}>} A&'|'&B) gave me same result.

PS: An example for a value is 'ROM Truck Model'

Please help.

[lockematthewp]

Count( DISTINCT {<C-={'ROM'}>} A&'|'&B) will only exclude rows where C = 'Rom' exactly. You need to use a wildcard search (*) to look for Rom anywhere in the word. Like: Count( DISTINCT {<C-={'*ROM*'}>} A&'|'&B) or Count( DISTINCT {<C-={'ROM*'}>} A&'|'&B) to only remove values that start with ROM.

[sunny_talwar · Accepted solution]

Try this with wildmatch (*) within double quotes.... so instead of 'ROM', you would use "ROM*"

=Count( DISTINCT {<C -= {"ROM*"}>} A&'|'&B)

[abhi1693r]

Hey,

How can I have two conditions in this one line. Lets say ROM and COM.

[sunny_talwar]

This

=Count( DISTINCT {<C -= {"ROM*", "*COM*"}>} A&'|'&B)

[abhi1693r]

What if "COM" is in another column, lets say 'P'?

[abhi1693r]

Also, Is there a work around. In some of my cases, this formula does not work!

Help appreciated.

[sunny_talwar]

Not sure what you mean... can you share an example to show where it doesn't work?

[abhi1693r]

Hi Sunny,

It will be difficult to give an example, but I will try.

I have table A LEFT JOIN(A) table B. 

Table A has a few extra rows which are added to the result after the commons are selected (Just how a left join works)

Now I am using =Count( DISTINCT {<C-={"ROM"}>} A&'|'&B) and the system is still counting those "extra rows" which got added from in Table A.

Now I think, the formula is working. But still I don't know why its not filtering properly.

Also tried, Count( DISTINCT {<C-={"ROM"}>} {<P-={"COM"}>} A&'|'&B)

Thanks for your patience.

[abhi1693r]

I have a feeling it is filtering with ROM condition first and then applying A&'|'&B. Because some rows are getting removed when we use C-=ROM