countif

vinod9111

Active Member
Joined
Jan 21, 2009
Messages
426
Hi all,

can i use the countif question to make it count till a particular row based on thevalue . for instance if i want the countif till value 45 is met i.e i want countif to count till the row where 45 value is housed

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17 x:str="VALUES ">VALUES </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>40</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>40</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>45</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>40</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>40</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>30</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>47</TD></TR></TBODY></TABLE>

i hope am clear with my question. if required further clarification let me know.

any help will be appreciated.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Hello

This perhaps:

Excel Workbook
ABC
1VALUES
240125
340
445
540
640
730
847
9
Index



The MATCH formula is used to locate the position of 45 in the column.

The SUMPRODUCT then sums all numbers where the row number is less than or equal to the row position of 45.
 
Upvote 0
Thanx Jon for your help,

the formula gives me sum total , i need to count it. The real problem is also if there is a duplication in the value.say for instance i need to count till value 40 there are 4 40. any idea on this

Thanx for time once again.
 
Upvote 0
Which 40 would you want to stop on? The first one?
 
Upvote 0
Ignore this for now based on your last answer!!!
 
Upvote 0
put this in a module in VBA

Function CountTo(rng As Range, target, occurance)
Code:
For Each Item In rng
    If Item.Value <> target Then
        currentcount = currentcount + 1
    Else
        If occurancecount = occurance Then
            CountTo = currentcount
            Exit Function
        Else
            occurancecount = occurancecount + 1
        End If
    End If
Next Item
CountTo = currentcount
End Function


then use the formula

=countto(A1:A100,40,3)


=countto(RANGE, TARGET, OCCURANCE)
 
Upvote 0
You could try a custom function like this...
Code:
Function CntIf(rCell As Range, Tar As Integer, Str1 As String)

For Each rCell In rCell.Cells
 If rCell = Tar Then
   GoTo jump1
 End If

 If rCell = Str1 Then x = x + 1
Next

jump1:
CntIf = x
End Function
And use it in your sheet like...

=CntIf(A1:A10,45,1)
=CntIf(RANGE,NUMBER TO STOP AT,NUMBER TO COUNT)

A1:A10, being the range
45, being the number to stop at
1, being what number you want to count

Hope this helps
 
Last edited:
Upvote 0
corrected

Code:
Function CountTo(rng As Range, target, occurance)

For Each Item In rng
    If Item.Value <> target Then
        currentcount = currentcount + 1
    Else
        If occurancecount = occurance Then
            CountTo = currentcount
            Exit Function
        Else
            occurancecount = occurancecount + 1
            currentcount = currentcount + 1
        End If
    End If
Next Item
CountTo = currentcount
End Function
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top