Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Errors in COUNT, COUNTA, COUNTIF when counting merged cells | Excel Worksheet Functions | |||
How do I use countif to count values excluding blank cells | Excel Worksheet Functions | |||
counting cells (COUNTIF) based on two or more criteria | Excel Worksheet Functions | |||
How do I set up a formula to repeat 5 consecutive cells in a colum | Excel Worksheet Functions | |||
How do I set up a formula to repeat 5 consecutive cells in a colum | Excel Worksheet Functions |