Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Multiple Count IFs

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Lets say I have the following spreadsheet:

    ColA ColB
    1 pass
    1 fail
    1 pass
    2 pass
    1 pass
    3 fail
    2 pass

    In another cell I want to count how many "1" in column A were "pass". I tried to write a count if statement, but it did not work. Here's what I tried: =((COUNTIF(A:A,"1"))*AND((COUNTIF(B:B,"pass")))). This only returns the number of 1s in collumn A.

    Can anyone help with single formula?

    Thanks,
    Jim

  2. #2
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You'll want to enter the following formula and press control-shift-enter (same time) once it's in the formula bar:

    =SUM((A1:A65536=1)*(B1:B65336="pass"))

    Should look like this:

    {=SUM((A1:A65536=1)*(B1:B65336="pass"))}

    Hope this helps.

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-03-19 15:14 ]

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

    Default

    On 2002-03-19 15:00, jereece wrote:
    Lets say I have the following spreadsheet:

    ColA ColB
    1 pass
    1 fail
    1 pass
    2 pass
    1 pass
    3 fail
    2 pass

    In another cell I want to count how many "1" in column A were "pass". I tried to write a count if statement, but it did not work. Here's what I tried: =((COUNTIF(A:A,"1"))*AND((COUNTIF(B:B,"pass")))). This only returns the number of 1s in collumn A.

    Can anyone help with single formula?

    Thanks,
    Jim
    Given that the sample you provided (with labels added)

    {"Studuent","Result";1,"pass";1,"fail";1,"pass";2,"pass";1,"pass";1,"fail";2,"pass"}

    is in A1:B8,

    you can also use:

    =SUMPRODUCT((A2:A8=1)*(B2:B8="pass"))

    where, if desired, 1 and pass can be in cells of their own and their cell refs then can be used in the formula.

    Another way would be to use DCOUNT:

    =DCOUNT(A1:B8,1,D1:E2)

    where D1 houses the label 'Student', D2 the criterion/condition 1, E1 the label 'Result', and E2 the criterion/condition "pass" (without double quotes").

    Aladin



    [ This Message was edited by: Aladin Akyurek on 2002-03-19 15:57 ]

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks. The only one I could get to work correctly was the SUMPRODUCT, but it works great.

    Thanks again.
    Jim

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Florida
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If you take the formula from Nate and correct the type o it works fine.....

    =SUM((A1:A65336=1)*(B1:B65336="pass"))

  6. #6
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Nice catch Kinky, I thought I lined both ranges up but no such luck.

  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    When I use =SUM((A1:A65336=1)*(B1:B65336="pass")), I get a result of 1 when clearly I have several that match.


    =SUMPRODUCT((A2:A21=1)*(B2:B21="pass")) still works however.

    Thanks,
    Jim

  8. #8
    Legend just_jon's Avatar
    Join Date
    Sep 2002
    Location
    Alabama/State of Disarray
    Posts
    10,473
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    First - do you really need to look at 65536 rows?

    SUMPRODUCT instead of SUM, and shorten the range -- SUMPRODUCT does not like whole column arrays.
    just_jon
    Book of the Month: I'm Not Really an MVP, I Just Play One on TV [j. jon, 2004]

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

    Default

    Quote Originally Posted by jereece
    When I use =SUM((A1:A65336=1)*(B1:B65336="pass")), I get a result of 1 when clearly I have several that match.
    The range in A must be numeric and the range in B should not have any spaces or non-visible chars around the values it houses. Try to run ASAP Utilities or the TrimAll macro (available in some threads on this site) on the ranges of interest to exclude that problem.

  10. #10
    New Member
    Join Date
    Mar 2002
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =SUMPRODUCT((A2:A8=1)*(B2:B8="pass"))

    This seems to work for me. One last question, is there a way to get this to work with wild cards (i.e. "*pass*")? I have one database I want to use this with and the entries are not 100% consistent. If I could use a wild card, that would eliminate my problem.

    I tried for example =SUMPRODUCT((A2:A8=1)*(B2:B8,*pass*)) but keep getting error messages.

    Can anyone help?

    Thanks,
    Jim

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
  •