COUNTIF With Non-Continuous Cells

[Guest]

I think I used to know how to do this, but I'm on a new version of Excel
(Xl2003) so maybe that wouldn't work anyway.

I want to perform a simple COUNTIF, except the cells I want the function to
examine are not contiguous. Essentially, I need the function to be something
like the following:

COUNTIF((C1,E1,H1,J1,N1),">=1")

However that, and other variations I've tried, don't seem to work. This has
got to be possible, but I think I need a pointer here. Thanks in advance.

 

[Domenic]

Try...

=SUMPRODUCT(--ISNUMBER(MATCH(COLUMN(C1:N1)-COLUMN(C1)+1,{1,3,6,8,12},0)),
--(C1:N1>=1))

or

=SUMPRODUCT(COUNTIF(INDIRECT({"C1","E1","H1","J1","N1"}),">=1"))

Also, if your data contains whole numbers, try...

=INDEX(FREQUENCY((C1,E1,H1,J1,N1),0),2)

Hope this helps!

 

[Don Guillett]

countif can't do this. you would need to
if(c1>0,1)+etc

or a UDF (macro to make a custom formula) using an array of c1,e1,n1,etc
Place in a REGULAR module and use as any formula =countnon()

Function countnon()
Application.Volatile
myarray = Array([c1], [e1], [h1], [n1])
For Each c In myarray
If IsNumeric(c) And c >= 1 Then mytot = mytot + 1
Next
countnon = mytot
End Function

 

[Guest]

Excellent!!

=SUMPRODUCT(COUNTIF(INDIRECT({"C1","E1","H1","J1","N1"}),">=1"))

That's exactly what I needed. I knew there was a way to artifically create a
range somehow. Thanks a million!