Named range in countif

Question

I am trying to use countif on a series of numbers that are not adjacent.  I have put them in a named range called hourly_totals.  If the hourly total is greater than zero, I want it counted.  I tried the following, but I am not getting the syntax to work:

=countif(hourly_totals,>0)

=countif("hourly_totals",>0)

=countif(hourly_totals,">0")

=countif("hourly_totals",">0")

I found that if hourly_totals are adjacent cells, then it works.  How do I change this to get it to work on non adjacent cells?  I have a brute force way of using a otherwise blank column with countif on individual cells, and totaling the column, but I am looking for a better way.

Excel XP on Windows XP

Answer 1

Hi Brad,

Thanks for the reply.  If my solution does not meet you needs I would definitely try Hans' solution.  I tried it and it worked great! 

Regards,

John

Answer 2

Your equation is a bit cumbersome with 24 hours to list, but I see the logic in it.  It is pretty much what I am doing in two steps using an additional column to sum up individual countifs.  I already have the hours totaled individually, and all values are zero or positive integers.  It has one advantage in that your way it is either there or not.  Mine relies on other cells that can be mistakenly changed and the result may not show the error.

The spreadsheets are traffic counts and I am trying to automate a label in a grand totals row with the number of hours that traffic was actually counted by checking for positive totals in the hours.  It is a minor item, but one that is easily overlooked come crunchtime on a project and this would eliminate one step in thousands that can make us look bad if wrong.

Answer 3

hourly_totals equals U24, U37, U50, U63, etc.  How to I adapt your VBA function to reference either the named range hourly_totals or just list the cells?  Do I replace "rng" with "hourly_totals"?

Answer 4

Hi,

Hans is correct, the Countif function only works with contiguous ranges.  Try the following formula:

=COUNTIF(A1:A10,">0")+(COUNTIF(B1:B10,">0")+COUNTIF(F1:F10,">0"))

Regards,

John

Answer 5

COUNTIF (and COUNTIFS, SUMIF etc.) only work with contiguous ranges. So you have to use a workaround.

In this specific situation you could use a custom VBA function:

Function CountPositive(rng As Range) As Long

    Dim cel As Range

    Dim n As Long

    For Each cel In rng.Cells

        If IsNumeric(cel.Value) And cel.Value > 0 Then

            n = n + 1

        End If

    Next cel

    CountPositive = n

End Function

Use like this:

=CountPositive(hourly_totals)