COUNTIF With Non-Continuous Cells
 

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.
--
Hmm...they have the Internet on COMPUTERS now!

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!

In article ,
MDW wrote:

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.

countif can't do this. you would need to
if(c10,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

--
Don Guillett
SalesAid Software

"MDW" wrote in message
...
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.
--
Hmm...they have the Internet on COMPUTERS now!

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!
--
Hmm...they have the Internet on COMPUTERS now!


"Domenic" wrote:

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!

In article ,
MDW wrote:

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.