# Countif/Countifs formula question

#### Kyroc

##### New Member
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.

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

#### JoeMo

##### MrExcel MVP
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
Try...

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

Hope this helps!

#### JoeMo

##### MrExcel MVP
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

##### New Member
Thank you both for your help. it worked 