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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,118
Messages
5,835,495
Members
430,360
Latest member
Ajonode

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.

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

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