Countif/Countifs formula question

[Kyroc]

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

 

[JoeMo]

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)

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]

Try...

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

Hope this helps!

 

[JoeMo]

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.

 

[Kyroc]

Thank you both for your help. it worked