Countif/Countifs formula question

Kyroc

New Member
Joined
Sep 9, 2014
Messages
7
Hello

I am trying to write a formula that will count only certain rows in a column if the result is greater than 0. I do not want every row included in the count.

Normally if I wanted every row the formula would be =countif(I4:I417,">0") however, because I only want particular rows in the column, the formula I have used is

=COUNTIF((I12,I21,I30,I39,I48,I57,I66,I75,I84,I93,I102,I111,I120,I129,I138,I147,I156,I165,I174,I183,I192,I201,I210,I219,I228,I237,I246,I255,I264,I273,I282,I291,I300,I309,I318,I327,I336,I345,I354,I363,I372,I381,I390,I399,I408,I417),">0")

This is giving me the result of value! in my cell however. I also tried doing it with countifs formula but cant get that to work either.

Any advice would be appreciated
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,326
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
COUNTIF requires a contiguous range. Here's a UDF alternative. First, select all the cells in your "particular" rows in col I. Now name the selection (i.e create a named range) "myCells" (w/o the quote marks). Next install the UDF below, then in the cell of your choice enter: =customCOUNTIF(myCells)

Code:
Function CustomCountIf(R As Range) As Long
Dim c As Range, Ct As Long
For Each c In Range("myCells")
    If Application.IsNumber(c.Value) Then
        If c.Value > 0 Then
            Ct = Ct + 1
        End If
    End If
Next c
CustomCountIf = Ct
End Function
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,389
Try...

=SUMPRODUCT(--(MOD(ROW(I12:I417)-ROW(I12),9)=0),--(I12:I417>0))

Hope this helps!
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,326
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Try...

=SUMPRODUCT(--(MOD(ROW(I12:I417)-ROW(I12),9)=0),--(I12:I417>0))

Hope this helps!
Nice Domenic - I didn't take the time to notice that there is a pattern to the OP's 'particular" rows.:mad:
 

Watch MrExcel Video

Forum statistics

Threads
1,114,550
Messages
5,548,688
Members
410,865
Latest member
siglertl
Top