Possible and Excluded value calculation with filtering criteria

[AH]

Hi,

I am trying to count the number of possible values and excluded values (based on filters criteria)  when i select values in another field. 

For example, If i select '500' in Column2 list box the possible value in the column1 list box is 'AB_14_0'. My goal is to find the distinct number of values in column1 where any value has a  '_14_' in it while its possible or excluded. _14_ is a dynamic value in the real data.

In my case when i select 500 there's total of 9 values in column1 that has _14_  in the value.

Thanks in advance!

Regards

[andrey_krylov]

Hi, not entirely clear what you need to get but try this

Count({1< Column1 = {"*$(=Mid(Column1, 3, 4))*"} >} Column1)

[AH]

Hi Andrey_krylov,

Thank you for your replies. With a different mixture of values in column1, your formula dont seem to give the right values. Could you take a look?

Thanks in advance.

Regards,

AH

[andrey_krylov]

Maybe this

Count({1< Column1 = {"*$(=Left(Right(Column1, 5), 4))*"} > } Column1)

or this

Count({1< Column1 = {"*_$(=SubField(Column1, '_', 2))_*"} > } Column1)

[AH]

Hi Andrey_krylov,

Thank you very much again. Your solution is totally correct except i have a little nuances in the actual data. If by any chance Value2 has 1 value refer to 2 values in Column 1  then how the calculation would look like. 

For example, if i select 527 in Column2 then I would like to count only the values with _14_ not the _16_ which is a nuance in the data. There are other cases like this in the actual data. 

Is that possible?

Thanks in Advance.

Regards,

SH

[andrey_krylov · Accepted solution]

Try to play around with min/max

=Count({1< Column1 = {"*_$(=Min(SubField(Column1, '_', 2)))_*"} > } Column1)

[AH]

Excellent! Thanks Andrey_krylov!!

Regards,

SH