Results 1 to 14 of 14
  1. #1
    New Lounger
    Join Date
    Jan 2009
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    I think that you mistyped the formula in cell B1
    Regards
    Don

  4. #4
    New Lounger
    Join Date
    Jan 2009
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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).
    Attached Files Attached Files

  6. #6
    New Lounger
    Join Date
    Jan 2009
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #8
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    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.
    Regards
    Don

  9. #9
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    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>
    Regards
    Don

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #11
    New Lounger
    Join Date
    Jan 2009
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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.
    Attached Files Attached Files

  13. #13
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Brantford, Ontario, Canada
    Posts
    2,391
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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!
    Christopher Baldrey

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •