Results 1 to 14 of 14

20090119, 19:37 #1
 Join Date
 Jan 2009
 Posts
 4
 Thanks
 0
 Thanked 0 Times in 0 Posts
Countif for nonconsecutive cells (Excel 2000)
Hi:
I need your help desperately. I have a data (a value for each hour of the day for the whole year) the I want to use Countif. I could do it manually one by one, like Countif (a1:a23. "1") for January first, Countif (a24:a47, "1") for January 2 e.t.c. As you can see it would be quite tedious to do this for the whole year. When i try to copy and paste the formula, it does not work because instead Countif (a47....) it will do Countif (a3.....) Is there a way you can do this with out having to type all the formulas? Your help would be greatly appreciated.

20090119, 19:50 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Countif for nonconsecutive cells (Excel 2000)
Welcome to Woody's Lounge!
Shouldn't that be A1:A24 for January 1 if there is a value for each hour of the day?
You could enter this formula in B1:
=COUNTIF(OFFSET($A$1:$A$24,24*(ROW()1),0),1)
and fill down.
B1 will contain the number of cells in A1:A24 that contain 1.
B2 will contain the number of cells in A25:A48 that contain 1.
etc.

20090119, 20:10 #3
 Join Date
 Jul 2001
 Location
 Ottawa, Ontario, Canada
 Posts
 1,609
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Countif for nonconsecutive cells (Excel 2000)
I think that you mistyped the formula in cell B1
Regards
Don

20090119, 20:13 #4
 Join Date
 Jan 2009
 Posts
 4
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Countif for nonconsecutive cells (Excel 2000)
Hi Hans:
I did what you mentioned, and on B2 I got =COUNTIF(OFFSET($A$1:$A$24,24*(ROW()1),0),1) and the same for B3, B4.........am i doing something wrong?

20090119, 20:14 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Countif for nonconsecutive cells (Excel 2000)
I don't understand where the 27 comes from. I have attached a small sample workbook with data for 7 days in A1:A168, and formulas in B1:B7.
Does that give you a clue? If not, could you attach a stripped down copy of your workbook (remove all confidential data).

20090119, 20:15 #6
 Join Date
 Jan 2009
 Posts
 4
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Countif for nonconsecutive cells (Excel 2000)
I meant to say =COUNTIF(OFFSET($A$1:$A$24,24*(ROW()1),0),1)

20090119, 20:17 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Countif for nonconsecutive cells (Excel 2000)
That is correct. The ROW() function makes the OFFSET work on a different range depending on the cell that contains the formula: in B1, ROW() = 1, in B2, ROW() = 2 etc.
For B1, 24*(ROW()1) = 0
For B2, 24*(ROW()1) = 24
For B3, 24*(ROW()1) = 48
etc.
so the OFFSET is 0 rows, 24 rows, 48 rows etc.

20090119, 20:19 #8
 Join Date
 Jul 2001
 Location
 Ottawa, Ontario, Canada
 Posts
 1,609
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Countif for nonconsecutive cells (Excel 2000)
That is as it should be. Cell B2 will evaluate A25:A48 as a consequence of the Offset.
Regards
Don

20090119, 20:21 #9
 Join Date
 Jul 2001
 Location
 Ottawa, Ontario, Canada
 Posts
 1,609
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Countif for nonconsecutive cells (Excel 2000)
Apologies for butting in. <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15>
Regards
Don

20090119, 20:26 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Countif for nonconsecutive cells (Excel 2000)
No problem at all! On the contrary, I appreciate your additional explanation! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

20090119, 20:28 #11
 Join Date
 Jan 2009
 Posts
 4
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Countif for nonconsecutive cells (Excel 2000)
Wow!!!! It worked; I will try to understand it how it worked, but it might be beyond my understanding!!! Thx a lot friends!

20090119, 20:39 #12
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Countif for nonconsecutive cells (Excel 2000)
You're welcome.
The attached version of the workbook contains an attempt at explaining the formula by adding intermediate results in columns C through G.

20090119, 22:48 #13
 Join Date
 Jan 2001
 Location
 Brantford, Ontario, Canada
 Posts
 2,391
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Countif for nonconsecutive cells (Excel 2000)
And I thank you both for solving this problem, and yonatan for posting this question. I've suggested this website to many people at work previously, and I think yonatan is the first to visit in a long time!
Now I've learned something new too!Christopher Baldrey

20090119, 22:53 #14
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Countif for nonconsecutive cells (Excel 2000)
Thanks, nice to hear that!