# Thread: Countif for non-consecutive cells (Excel 2000)

1. ## Countif for non-consecutive 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.

2. ## Re: Countif for non-consecutive 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.

3. ## Re: Countif for non-consecutive cells (Excel 2000)

I think that you mistyped the formula in cell B1

4. ## Re: Countif for non-consecutive 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?

5. ## Re: Countif for non-consecutive 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).

6. ## Re: Countif for non-consecutive cells (Excel 2000)

I meant to say =COUNTIF(OFFSET(\$A\$1:\$A\$24,24*(ROW()-1),0),1)

7. ## Re: Countif for non-consecutive 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.

8. ## Re: Countif for non-consecutive cells (Excel 2000)

That is as it should be. Cell B2 will evaluate A25:A48 as a consequence of the Offset.

9. ## Re: Countif for non-consecutive cells (Excel 2000)

Apologies for butting in. <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15>

10. ## Re: Countif for non-consecutive 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>

11. ## Re: Countif for non-consecutive 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!

12. ## Re: Countif for non-consecutive 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.

13. ## Re: Countif for non-consecutive 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!

14. ## Re: Countif for non-consecutive cells (Excel 2000)

Thanks, nice to hear that!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•