Results 1 to 9 of 9

Thread: COUNTIF (2003)

  1. #1
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    COUNTIF (2003)

    Hi everyone,

    I am try to count all of the fields in colomn E if they meet certain criteria but only if colomn D meets certain criteria in the same row

    Heres where I'm at so far

    =COUNTIF(Sheet1!E:E,"A")+CountIF(Sheet1!E:E,"B") >>>>>I there a way to use AND/OR in this formula sort of like =COUNTIF(Sheet1!E:E,"A"Or"B")

    The above works for counting "A" and "B" from Sheet 1 E:E but...

    Now I need to count "C" and "D" but only if Sheet 1, coloum D on the matching row = "Internal Incomplete"

    I tried =IF(Sheet1!D="Internal Incomplete",(COUNTIF(Sheet1!E:E,"C"))) >>>>> Did not work

    Many thanks
    Regards
    Gerbil (AKA Kevin)

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

    Re: COUNTIF (2003)

    SUMIF and COUNTIF do not support multiple conditions (Excel 2007 has new functions SUMIFS and COUNTIFS that do). You can use SUMPRODUCT instead:

    =SUMPRODUCT((Sheet1!D11000="Internal Incomplete")*(Sheet1!E1:E1000="C"))

    (This does not work with entire columns D and E:E, you have to specify finite ranges)

  3. #3
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: COUNTIF (2003)

    Thanks Hans

    =SUMPRODUCT((Sheet1!D11000="Internal Incomplete")*(Sheet1!E1:E1000="C"Or"B"))
    Regards
    Gerbil (AKA Kevin)

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

    Re: COUNTIF (2003)

    Is that a question?

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: COUNTIF (2003)

    I think your question how to make this work:
    =SUMPRODUCT((Sheet1!D11000="Internal Incomplete")*(Sheet1!E1:E1000="C"Or"B"))

    So I would answer it with:

    =SUMPRODUCT((Sheet1!D11000="Internal Incomplete")*((Sheet1!E1:E1000="C") +(Sheet1!E1:E1000="B")))

    The "*" acts as an "AND" and the "+" acts like am "OR"

    Steve

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: COUNTIF (2003)

    Alternatively:
    <code>=SUMPRODUCT((Sheet1!D11000="Internal Incomplete")*(Sheet1!E1:E1000={"C","B"})) </code>
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: COUNTIF (2003)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> I think it should be:

    =SUM((D11000="Internal Incomplete")*<font color=red>(</font color=red>(E1:E1000="C")+(E1:E1000="B")<font color=red>)</font color=red>)

    Your formula will calculate for (D="Internal Incomplete" AND E= "C") OR just E="B". If E= B then it would not matter what column D has in it....

    Steve

  8. #8
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: COUNTIF (2003)

    Yep, you are correct, I hadn't tested it fully with a mixture of letters,

    I'll change mine now
    Jerry

  9. #9
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: COUNTIF (2003)

    <P ID="edit" class=small>(Edited by Jezza on 20-Aug-08 16:23. To add parenthesis, as per Steve's message below)</P>As an another alternative using the sum function as an array formula try:

    =SUM((D11000="Internal Incomplete")*((E1:E1000="C")+(E1:E1000="B")))

    and then press Ctrl+Shift+Enter whilst the cell that holds this formula has the focus. It should look like this afterwards:

    {=SUM((D11000="Internal Incomplete")*((E1:E1000="C")+(E1:E1000="B")))}

    This is an unusual anomaly as using sum in this way actual converts it to counting....enjoy <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Jerry

Posting Permissions

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