Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: syntax : multiple ranges in countif

  1. #1
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi all,

    I should know this but just can't get it

    I'm trying to use countif on 24 seperate ranges (A1:A7 and A11:A17 and A21:A27 and A31:A37 etc etc etc)

    do I have to use 24 seperate countif statements and add them or can I simplify it ?

    =COUNTIF(A1:A7,5)+COUNTIF(A11:A17,5)+COUNTIF(A21:A27,5)+COUNTIF(A31:A37,5) etc etc etc

    many thanks
    Chris



  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,645
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    Chris,

    > I'm trying to use countif on 24 seperate ranges (A1:A7 and A11:A17 and A21:A27 and A31:A37 etc etc etc)

    >do I have to use 24 seperate countif statements and add them or can I simplify it ?

    =COUNTIF(A1:A7,5)+COUNTIF(A11:A17,5)+COUNTIF(A21:A27,5)+COUNTIF(A31:A37,5) etc etc etc

    I was going to say yes... But I couldn't resist trying out an idea stirred up by an another recent question at this board & being amazed by your

    "I should know this but just can't get it"

    that is heading your question:

    Consider the following sample:

    {5;2;5;"";5;8;5.00;"zxa";"";"";5;5;7}

    in A1:A13, where "" stands for an empty cell.

    I'm interested in counting 5's in A1:A3 and A11:A13.

    =SUMPRODUCT((COUNTIF(INDIRECT(ADDRESS({1;11},1)&":"&ADDRESS({3;13},1)),5)))

    appears to compute the desired count.

    Aladin

    [ This Message was edited by: Aladin Akyurek on 2002-03-03 11:18 ]

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    amazed ?

    edit --> arrrgh, yup

    [ This Message was edited by: Chris Davison on 2002-03-03 11:59 ]

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    fantastic

    using your example and extrapolating

    =SUMPRODUCT((COUNTIF(INDIRECT(ADDRESS({1;11;21;31;41;51},1)&":"&ADDRESS({3;13;23;33;43;54},1)),5)))

    etc etc for the 24 ranges is much much shorter



    Ohhh - I just changed the last 54 to 59 and populated a few more "5"s and it still works - had you anticipated it working without equal-sized ranges ? ie cos it's not an array formula ?

    wow

    [ This Message was edited by: Chris Davison on 2002-03-03 11:52 ]

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,645
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    > using your example and extrapolating

    =SUMPRODUCT((COUNTIF(INDIRECT(ADDRESS({1;11;21;31;41;51},1)&":"&ADDRESS({3;13;23;33;43;54},1)),5)))

    etc etc for the 24 ranges is much much shorter

    Yes, that was exactly my intent.

    > Ohhh - I just changed the last 54 to 59 and populated a few more "5"s and it still works -

    Yes, it will.

    > does this mean it still works without equal-sized ranges ?

    The issue of "unequal-sized ranges" does not arise in this formula. What happens is that COUNTIF gets fed with different ranges at a time, each time with the same condition (looking for 5). The result is a constant array consisting of individual counts produced by COUNTIF, which SUMPRODUCT then sums in its turn.

    > ie cos it's not an array formula ?

    It's an inherently array formula (which does not need entering with the control+shif+enter combination though). If you'd like to know more about SUMPRODUCT, you might want to have a look at

    hhtp://www.mrexcel.com/wwwboard/messages/8961.html

    Aladin

    [ This Message was edited by: Aladin Akyurek on 2002-03-03 12:08 ]

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    bookmarked..... I'd actually been searching for that entry for a while

    thanks

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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