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

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
Joined
May 26, 2009
Messages
17,246
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,309
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,246
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,109,541
Messages
5,529,436
Members
409,877
Latest member
DDhol
Top