Hi Everyone,
I have the following dataset

150.1 153.2 154.3 155.1 155.1 157.1 157.4


I would like to count how many cells have 0.1 given the condition that they are between those cells that start with 0.3 and end with 0.4

So in the above example the answer would be 3.

I tried COUNTIF function but am having trouble.

Can someone help?

Thanks

Cherian

On Tuesday, 24 March 2015 07:11:22 UTC+11, wrote:
Hi Everyone,
I have the following dataset

150.1 153.2 154.3 155.1 155.1 157.1 157.4


I would like to count how many cells have 0.1 given the condition that they are between those cells that start with 0.3 and end with 0.4

So in the above example the answer would be 3.

I tried COUNTIF function but am having trouble.

Can someone help?

Thanks

Cherian

Could you try to explain this a bit more clearly? You start off with a particular dataset, but then it seems this dataset is just an example ("in the above example ..."). So what does a typical dataset for your problem look like? Are the numbers always ordered from left to right? Is there always one number ending in .3 and later one ending in .4? And are you only looking at the numbers after the decimal points?

Howard

Hi,

Am Mon, 23 Mar 2015 13:11:21 -0700 (PDT) schrieb :

150.1 153.2 154.3 155.1 155.1 157.1 157.4

I would like to count how many cells have 0.1 given the condition that they are between those cells that start with 0.3 and end with 0.4

try it with an UDF:

Function myCount(myRng As Range) As Long
Dim i As Long

With myRng
For i = 1 To .Cells.Count
If Round(.Cells(i) - Int(.Cells(i)), 1) = 0.3 Then
Do
i = i + 1
If Round(.Cells(i) - Int(.Cells(i)), 1) = 0.1 Then
myCount = myCount + 1
End If
Loop Until Round(.Cells(i) - Int(.Cells(i)), 1) = 0.4
Exit For
End If
Next
End With
End Function

And call the function into the sheet with
=myCount(A1:G1)


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional