Subtracting Extracted numbers from a string

[JeffGrant]

Hi All,

I have a situation where I would like to subtract the number of occurrences that the count of numbers that meets a condition can be subtracted from the starting number.

What does this mean?

I have a starting number of 4.
I have a string that contains 1,2,3
The criteria is to subtract the count of numbers < 4.
In this case, the count of numbers < 4 is 3, therefore 4 - 3 =1 is the result.

Or

I have a starting number of 4.
I have a string that contains 3,7,8
The criteria is to subtract the count of numbers < 4.
In this case, the count of numbers < 4 is 1, therefore 4 -1 = 3 is the result.

Or

I have a starting number of 4.
I have a string that contains 7,9,10,11
The criteria is to subtract the count of numbers < 4
In this case, the count of numbers , 4 is 0, therefore 4 -0 = 4 is the result.

I have no idea where to start.

I have attached a workbook with this example in it.

Ideally I would like this to be an excel cell formula, not a vba macro.

Any guidance is most appreciated.

Thanks

Jeff.

[Czeslaw]

I suggest this option

[Bo_Ry]

Please try
for 3 string number
=A4+SUM(-(--MID(B4,SEQUENCE(3,,,2),1)<A4))

or any string number
=A4+SUM(-(FILTERXML("<x><m>"&SUBSTITUTE(B4,",","</m><m>")&"</m></x>","//m")<A4))

[Fluff13]

Another option similar to Bo_Ry's

Please Login or Register  to view this content.

[JeffGrant]

Thank you all.

I am in total Awe of the knowledge of the people in this forum.

I am proud to work with you all.

Jeff.

[Fluff13]

Glad to help & thanks for the feedback