Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 173
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 173
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,231
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
average of 12 noncontiguous cells goss[_2_] Excel Discussion (Misc queries) 6 November 12th 07 11:26 PM
Adding noncontiguous cells FJ Excel Discussion (Misc queries) 9 December 23rd 06 05:54 PM
Using Multiple, Noncontiguous Ranges in COUNTIF? PBJ Excel Discussion (Misc queries) 0 October 11th 06 05:45 PM
noncontiguous cells Vivian Excel Discussion (Misc queries) 2 July 21st 06 01:00 AM
can you use countif function for noncontiguous cells rutledbr Excel Worksheet Functions 2 November 13th 05 10:15 PM


All times are GMT +1. The time now is 06:39 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"