CountIf formula

[Tigerexcel]

I've been playing around with some advanced functions and though I can get this to work with helper columns, I'd like to try and combine all of this into one function.

Let's say you have cell entries such as ABC3390 60-P, ABC2517 1350 45-N. Each of these are in separate cells. I need to extract the numerical score which fortunately is in the 9th position and so do that with =MID(A1,9,2), then I convert that to a value by =VALUE(MID(A1,9,2) and then need to use COUNTIF to return the number of values that exceed 49. Can these functions be combined into a one cell formula for convenience?

 

[Peter_SSs]

.. yes it is possible that someone could score less than 10 creating a formula error. I shared the solutions from the previous post with a co-worker who thought that there might be an easier way

Would either of these give you what you want?
{=SUM(--((0&MID(A1:A7,9,1))-4>0))}
=COUNTA(A1:A7)-COUNTIF(A1:A7,"*N")

 

[Peter_SSs]

.. guessing .. another possibility?
=SUMPRODUCT(--(RIGHT("N"&A1:A7,1)<>"N"))

 

[Tigerexcel]

Actually Peter all 3 of your latest offerings do the job!
2 questions. What does the 0& in front of the MID function do in your formula?
Of the 3 formulae, my favourite is the second one because it's the easiest one to understand. In my data though there are cells which look blank however are not. When I run the ISBLANK function on them, it returns FALSE, I can only surmise that there must be a space in some of the cells. This then throws the formula off because COUNTA counts those blank cells as 1.
The great thing about having all these options with formulae is that you can pick and choose a formula for whichever circumstances arise.

 

[Peter_SSs]

What does the 0& in front of the MID function do in your formula?

For the cells that have data, it means that a score of, say, 47 become 047 which does not change its numerical value.
However, for a blank cell MID(....,9,2) returns "" which causes an error when you try to do an arithmetic operation with it - as you found with Kirk's formula from post #2.
With my formula 0&"" becomes "0" which, whilst it is also a text value, gets coerced to the numerical value 0 when it has the 4 subtracted from it. This stops the formula from returning a #VALUE! error.

You said you had 2 questions but I could only see one.

 

[Tigerexcel]

For the cells that have data, it means that a score of, say, 47 become 047 which does not change its numerical value.
However, for a blank cell MID(....,9,2) returns "" which causes an error when you try to do an arithmetic operation with it - as you found with Kirk's formula from post #2.
With my formula 0&"" becomes "0" which, whilst it is also a text value, gets coerced to the numerical value 0 when it has the 4 subtracted from it. This stops the formula from returning a #VALUE! error.

You said you had 2 questions but I could only see one.

Thanks for the explanation. My second question which I'm not sure has an answer is can you easily locate the cells that have a space in them and nothing else? They look blank but Excel using CountA counts it as 1 when it shouldn't.

 

[Peter_SSs]

Are you sure they are blank? In your previous post you seemed unsure. Perhaps they contain "" or some other non-visible character other than a standard space character?
What does =LEN(A5) or =CODE(A5) return if pointed at one of those 'blank' cells?

 

[Tigerexcel]

Are you sure they are blank? In your previous post you seemed unsure. Perhaps they contain "" or some other non-visible character other than a standard space character?
What does =LEN(A5) or =CODE(A5) return if pointed at one of those 'blank' cells?

I tried this on another sheet and the Count formula worked fine, I'll see if I can replicate it again. Thanks again.

 

[Tigerexcel]

To Peter and/or anyone else who can chime in, I'd like to explore the "workarounds" referred to in post 12 & post 14. In post 12 there is an "N"&..... referenced, I suspect that helps to avoid the #VALUE errror.
I've searched on Google for more information on this but can't really find anything, is there somewhere where I can find out a bit more about this?

 

[Peter_SSs]

In post 12 there is an "N"&..... referenced, I suspect that helps to avoid the #VALUE errror.

No that is not to avoid an error value it is to avoid counting blank cells as a >49 score. My guess in post #12 was that counting scores >49 meant counting how many people "passed". That is, how many people got P, C, D, HD etc. A simpler way of looking at that in my mind was "how many people did not fail?". In other words how may cells did not end with "N"

Consider this example, looking at A1:A7

20 07 11.xlsm
	A
1	ABC3390 60-P
2	ABC2517 45-N
3	ABC2121 95-HD
4
5
6
7
Sample 1

How many do not end with N? The 4 blank cells do not end with N and also A1 and A3 do not giving a result of 6 when we really want a result of 2.
By using "N"&A1:A7 in my formula, the values being looked at become ..

20 07 11.xlsm
	A
1	NABC3390 60-P
2	NABC2517 45-N
3	NABC2121 95-HD
4	N
5	N
6	N
7	N
Sample 2

.. and now counting how many do not end with N the result is 2.

 

[Tigerexcel]

Thank you for the explanation. That is great creative thinking and I guess no website or book would contain such tricks.