formula to count the instances of a type of number in cells

[Doofus1]

Hi Folks,
I have a formula =COUNTIF(G835:I835, "<" & F835) + SUMPRODUCT(--IFERROR(SEARCH("_", G835:I835), 0)) but it does not do what I want it to do.
Could someone look at the uploaded spreadsheet and offer a formula that works ?
Thank you
Doofus1


Sorry , CORRECTION 1. Count the number of times the number in D2:F2 are below/lower than the number in C2
2. If any of those numbers are either underlined or bold, it is to be counted twice.
3. in D2:F2, numbers that are above the figure in C2 get a minus count of 1

MINUS

4 Count the number of times the number in D3:F3 are below/lower than the number in C2
5. If any of those numbers are either underlined or bold, it is to be counted twice.
6. in D3:F3, numbers that are above the figure in C2 get a minus count of 1


This has to repeated for the number in C3 too

Need help with writing a formula to achieve this

[JohnTopley]

A formula cannot check for underlining or bold: you will need VBA to do this.

[Doofus1]

Hi Mr Topley,
Not very conversant with VBA.
How and what do I do to achieve this?
Thank you

[Doofus1]

Or, can this be done if, for instance, if I typed the letter "W" next to the number eg 3W. ? Instead of using VBA - which I am not conversant with.

If anyone knows how to write this, feel free to contribute

[JeteMc]

This proposal employs three helper columns (G:I) populated using: =IF(ISTEXT(D2),LEFT(D2,LEN(D2)-1)+0,D2)
The following formula yields the expected result in cell K2:

Please Login or Register  to view this content.

In cell K3 it yields 2 instead of -1.
In cell J3 I don't understand how the minus three in (3-1)-3 is derived.

[Doofus1]

Hi JetMc,
Sorry for the delay in responding.
Thank you.!!
This works too.
Best regards
Doofus.

[AliGW]

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.