+ Reply to Thread
Results 1 to 3 of 3

Count cells in one range based on parameters in another range

  1. #1
    dave roth
    Guest

    Count cells in one range based on parameters in another range

    Hi;

    I want to count cells in one range on a worksheet that meet certain criteria
    ("Fatal", "Serious", "Minor", etc.) based on whether they are related to
    parameters in another range ("Gunshot", "Stabbing", etc.)

    In other words, where an occurrence has Gunshot, I need to count all
    occurrences of Fatal. The count serves as input to another cell, which is
    then graphed.

    I've been doing this by autofiltering, but that's a manual solution, and I
    would like to automate the reporting.

    I've tried
    =COUNTIF(ExtentofInjury_2005,"Fatal")+COUNTIF(InjuryType1_2005,"Gunshot"),
    which of course counts all gunshots and all fatals(trust me, we don't have 24
    fatal gunshots). And I know I could do a Pivot table; that's probably the
    best solution.

    I think I remember this coming up a few years back, but can't remember the
    solution.

    Tia for your assistance.

  2. #2
    Biff
    Guest

    Count cells in one range based on parameters in another range

    Hi!

    Try something like this:

    =SUMPRODUCT(--(A1:A100="Fatal"),--(B1:B100="Gunshot"))

    Or use cells to hold the criteria:

    C1 = Fatal
    D1 = Gunshot

    =SUMPRODUCT(--(A1:A100=C1),--(B1:B100=D1))

    Biff

    >-----Original Message-----
    >Hi;
    >
    >I want to count cells in one range on a worksheet that

    meet certain criteria
    >("Fatal", "Serious", "Minor", etc.) based on whether they

    are related to
    >parameters in another range ("Gunshot", "Stabbing", etc.)
    >
    >In other words, where an occurrence has Gunshot, I need

    to count all
    >occurrences of Fatal. The count serves as input to

    another cell, which is
    >then graphed.
    >
    >I've been doing this by autofiltering, but that's a

    manual solution, and I
    >would like to automate the reporting.
    >
    >I've tried
    >=COUNTIF(ExtentofInjury_2005,"Fatal")+COUNTIF

    (InjuryType1_2005,"Gunshot"),
    >which of course counts all gunshots and all fatals(trust

    me, we don't have 24
    >fatal gunshots). And I know I could do a Pivot table;

    that's probably the
    >best solution.
    >
    >I think I remember this coming up a few years back, but

    can't remember the
    >solution.
    >
    >Tia for your assistance.
    >.
    >


  3. #3
    dave roth
    Guest

    RE: Count cells in one range based on parameters in another range

    TYVM. The SUMPRODUCT function would not would occirred to me, but I've seen
    the answer before; dead brain cells.

    "Biff" wrote:

    > Hi!
    >
    > Try something like this:
    >
    > =SUMPRODUCT(--(A1:A100="Fatal"),--(B1:B100="Gunshot"))
    >
    > Or use cells to hold the criteria:
    >
    > C1 = Fatal
    > D1 = Gunshot
    >
    > =SUMPRODUCT(--(A1:A100=C1),--(B1:B100=D1))
    >
    > Biff
    >
    > >-----Original Message-----
    > >Hi;
    > >
    > >I want to count cells in one range on a worksheet that

    > meet certain criteria
    > >("Fatal", "Serious", "Minor", etc.) based on whether they

    > are related to
    > >parameters in another range ("Gunshot", "Stabbing", etc.)
    > >
    > >In other words, where an occurrence has Gunshot, I need

    > to count all
    > >occurrences of Fatal. The count serves as input to

    > another cell, which is
    > >then graphed.
    > >
    > >I've been doing this by autofiltering, but that's a

    > manual solution, and I
    > >would like to automate the reporting.
    > >
    > >I've tried
    > >=COUNTIF(ExtentofInjury_2005,"Fatal")+COUNTIF

    > (InjuryType1_2005,"Gunshot"),
    > >which of course counts all gunshots and all fatals(trust

    > me, we don't have 24
    > >fatal gunshots). And I know I could do a Pivot table;

    > that's probably the
    > >best solution.
    > >
    > >I think I remember this coming up a few years back, but

    > can't remember the
    > >solution.
    > >
    > >Tia for your assistance.
    > >.
    > >

    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1