countif

vinod9111

Active Member
Joined
Jan 21, 2009
Messages
425
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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
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.
 

vinod9111

Active Member
Joined
Jan 21, 2009
Messages
425
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.
 

vinod9111

Active Member
Joined
Jan 21, 2009
Messages
425

ADVERTISEMENT

Thanx Kgkev for your reply, i would like to stop on the 3rd
 

vinod9111

Active Member
Joined
Jan 21, 2009
Messages
425

ADVERTISEMENT

ok kgkev, help me with the formula if i want to stop at first one.
 

kgkev

Well-known Member
Joined
Jun 24, 2008
Messages
1,247
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)
 

Georgiboy

Board Regular
Joined
Nov 7, 2008
Messages
114
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:

kgkev

Well-known Member
Joined
Jun 24, 2008
Messages
1,247
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,046
Messages
5,639,757
Members
417,108
Latest member
Thein Than

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
Top