COUNTIF on NonContiguous Cells?

R

RoadKill

Okay, so I think my issue with my COUNTIF is that the cells are not next to
each other. This is my range: (C3,E3,G3,I3,K3,M3).

What I want to do is range all the cells in that range that have data
equivalent to 1.00.

What I am thinking is =COUNTIF(C3,E3,G3,I3,K3,M3, "=1"). But unfortunately
that doesn't work.

Thanks
 
M

Mike H

Try this

=SUMPRODUCT(--ISNUMBER(MATCH(COLUMN(C3:M3)-COLUMN(C3)+1,{1,3,5,7,9,11},0)),
--(C3:M3=1))

all in one line

Mike
 
R

RoadKill

Wow, that works. Thanks!

Mike H said:
Try this

=SUMPRODUCT(--ISNUMBER(MATCH(COLUMN(C3:M3)-COLUMN(C3)+1,{1,3,5,7,9,11},0)),
--(C3:M3=1))

all in one line

Mike
 
H

Harlan Grove

RoadKill said:
Okay, so I think my issue with my COUNTIF is that the cells are not next to
each other. This is my range: (C3,E3,G3,I3,K3,M3).

What I want to do is range all the cells in that range that have data
equivalent to 1.00.

What I am thinking is =COUNTIF(C3,E3,G3,I3,K3,M3, "=1"). But unfortunately
that doesn't work.

For counting, use FREQUENCY.

=INDEX(FREQUENCY((C3,E3,G3,I3,K3,M3),{0.999999999999999;1}),2)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top