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?

 

[KRice]

Try this, assuming you have values of interest in A1:A7 (adjust as necessary)
=SUMPRODUCT(--(VALUE(MID(A1:A7,9,2))>49))

 

[Tigerexcel]

Try this, assuming you have values of interest in A1:A7 (adjust as necessary)
=SUMPRODUCT(--(VALUE(MID(A1:A7,9,2))>49))

Thanks Kirk, I get a #VALUE error when I put data into A1:A7, if I restrict this to A1 only ie A1:A1 then it works.

 

[Michael M]

What about the code I provided in a previous thread, that sums ALL of the counts into a MsgBox....but it could go straight to a cell if required !

 

[Tigerexcel]

Thanks Kirk, I get a #VALUE error when I put data into A1:A7, if I restrict this to A1 only ie A1:A1 then it works.

It would seem that if there's an empty cell in the range, it throws the formula off and an error is returned.

 

[Amit Tandon]

Try this Array formula (Ctrl+Shift+Enter):

=SUM(--IFERROR((--MID(A2:A15,9,2)>49),0))

 

[Peter_SSs]

Let's say you have cell entries such as ABC3390 60-P, ABC2517 1350 45-N.

Is that really what your data might look like? Going by a previous thread thread of yours with similar data, isn't the score in that red value 45, not 13?

Also, going back to another comment in that previous thread, scores of less than 10 were possible meaning that the score for those would not be MID(A1,9,2)
Has that circumstance changed?

 

[Tigerexcel]

Is that really what your data might look like? Going by a previous thread thread of yours with similar data, isn't the score in that red value 45, not 13?

Also, going back to another comment in that previous thread, scores of less than 10 were possible meaning that the score for those would not be MID(A1,9,2)
Has that circumstance changed?

Good pickup, yes the 1350 is redundant and shouldn't be there and 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 through COUNTIF or possibly SUMPRODUCT, I did play around with them but couldn't quite get them to work.

 

[Tigerexcel]

Try this Array formula (Ctrl+Shift+Enter):

=SUM(--IFERROR((--MID(A2:A15,9,2)>49),0))

Thanks Amit, that works well for the sample data.

 

[Tigerexcel]

What about the code I provided in a previous thread, that sums ALL of the counts into a MsgBox....but it could go straight to a cell if required !

Yes thanks I will bear that in mind.