Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF on NonContiguous Cells?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF on NonContiguous Cells?
Try this:
=SUMPRODUCT(--(MOD(COLUMN(C3:M3)-COLUMN(C3),2)=0),--(C3:M3=1)) -- Biff Microsoft Excel MVP "RoadKill" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF on NonContiguous Cells?
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 "RoadKill" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF on NonContiguous Cells?
Wow, that works. Thanks!
"Mike H" wrote: 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 "RoadKill" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF on NonContiguous Cells?
You seem surprised it did!! Thank's for the feedback
"RoadKill" wrote: Wow, that works. Thanks! "Mike H" wrote: 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 "RoadKill" wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF on NonContiguous Cells?
RoadKill wrote...
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.9999999999 99999;1}),2) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
average of 12 noncontiguous cells | Excel Discussion (Misc queries) | |||
Adding noncontiguous cells | Excel Discussion (Misc queries) | |||
Using Multiple, Noncontiguous Ranges in COUNTIF? | Excel Discussion (Misc queries) | |||
noncontiguous cells | Excel Discussion (Misc queries) | |||
can you use countif function for noncontiguous cells | Excel Worksheet Functions |