Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
michaelberrier
 
Posts: n/a
Default Countif in non-consecutive cells

How can I do a countif in non-consecutive cells over a series of rows,
like evens and odds?. I tried this:
=COUNTIF($C$1:$Z$1:$C$3:$Z$3:$C$5:$Z$5:$C$7:$Z$7:$ C$9:$Z$9:$C$11:$Z$11:$C$13:$Z$13,"1")
but it counts everything on the even rows as well.

Thanks to all.

  #2   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default Countif in non-consecutive cells

=SUMPRODUCT((MOD(ROW(C1:Z13),2)=1)*(C1:Z13=1))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"michaelberrier" wrote in message
oups.com...
How can I do a countif in non-consecutive cells over a series of rows,
like evens and odds?. I tried this:

=COUNTIF($C$1:$Z$1:$C$3:$Z$3:$C$5:$Z$5:$C$7:$Z$7:$ C$9:$Z$9:$C$11:$Z$11:$C$13
:$Z$13,"1")
but it counts everything on the even rows as well.

Thanks to all.



  #3   Report Post  
Posted to microsoft.public.excel.misc
michaelberrier
 
Posts: n/a
Default Countif in non-consecutive cells

Bob,
Looks good. Now, what if your Countif criteria is text? I tried
inserting a "D" in place of both or either of the 1's below and got
something screwy.

Thanks again,
Bob Phillips wrote:
=SUMPRODUCT((MOD(ROW(C1:Z13),2)=1)*(C1:Z13=1))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"michaelberrier" wrote in message
oups.com...
How can I do a countif in non-consecutive cells over a series of rows,
like evens and odds?. I tried this:

=COUNTIF($C$1:$Z$1:$C$3:$Z$3:$C$5:$Z$5:$C$7:$Z$7:$ C$9:$Z$9:$C$11:$Z$11:$C$13
:$Z$13,"1")
but it counts everything on the even rows as well.

Thanks to all.


  #4   Report Post  
Posted to microsoft.public.excel.misc
Biff
 
Posts: n/a
Default Countif in non-consecutive cells

Hi!

Try this:

Is your criteria, "1", TEXT or NUMERIC?

Enclosing it in quotes " " makes it TEXT. A numeric 1 and a text 1 are not
the same.

=SUMPRODUCT((MOD(ROW(C1:Z13)-ROW(C1),2)=0)*(C1:Z13=1))

Biff

"michaelberrier" wrote in message
oups.com...
How can I do a countif in non-consecutive cells over a series of rows,
like evens and odds?. I tried this:
=COUNTIF($C$1:$Z$1:$C$3:$Z$3:$C$5:$Z$5:$C$7:$Z$7:$ C$9:$Z$9:$C$11:$Z$11:$C$13:$Z$13,"1")
but it counts everything on the even rows as well.

Thanks to all.



  #5   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default Countif in non-consecutive cells

Not both, just the latter

=SUMPRODUCT((MOD(ROW(C1:Z13),2)=1)*(C1:Z13="D1"))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"michaelberrier" wrote in message
ups.com...
Bob,
Looks good. Now, what if your Countif criteria is text? I tried
inserting a "D" in place of both or either of the 1's below and got
something screwy.

Thanks again,
Bob Phillips wrote:
=SUMPRODUCT((MOD(ROW(C1:Z13),2)=1)*(C1:Z13=1))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"michaelberrier" wrote in message
oups.com...
How can I do a countif in non-consecutive cells over a series of rows,
like evens and odds?. I tried this:


=COUNTIF($C$1:$Z$1:$C$3:$Z$3:$C$5:$Z$5:$C$7:$Z$7:$ C$9:$Z$9:$C$11:$Z$11:$C$13
:$Z$13,"1")
but it counts everything on the even rows as well.

Thanks to all.




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
Errors in COUNT, COUNTA, COUNTIF when counting merged cells Outback Excel Worksheet Functions 1 February 7th 06 05:29 PM
How do I use countif to count values excluding blank cells Glenda Excel Worksheet Functions 4 January 30th 06 05:22 PM
counting cells (COUNTIF) based on two or more criteria Tricia S. Excel Worksheet Functions 10 March 17th 05 03:17 PM
How do I set up a formula to repeat 5 consecutive cells in a colum jbsand1001 Excel Worksheet Functions 1 January 7th 05 06:59 PM
How do I set up a formula to repeat 5 consecutive cells in a colum jbsand1001 Excel Worksheet Functions 0 January 7th 05 06:43 PM


All times are GMT +1. The time now is 10:14 AM.

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"