Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sum using three criteria (2002)

    Calculating total number of occurrences using three criteria. The attached spread sheet explains all.
    I need to be able to get a total number of occurrences based on company name, incident type within a certain month.
    Any help would be greatly appreciated.

    <img src=/S/aussie.gif border=0 alt=aussie width=21 height=22> <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Sum using three criteria (2002)

    Hi Dean,

    Try the attached. I've used array formulae to do this, setting up one in the top-right corner for each make & copying down & accross. Note that named ranges for your table would make this easier than relying on cell references.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum using three criteria (2002)

    Thanks for the help. I tried to use the array formulae (with changed references) in the speadsheet attached and for some reason it does not seem to calculate. Also thinking about it further also need to count (ie Jan 03 v Jan 04) by year. You can see the data in Working Incident 04 (tab) and the required numbers in Stats Details (tab). Would you mind suggesting what I need to do - very much appreciated. <!help=01>Help 01<!/help> <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

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

    Re: Sum using three criteria (2002)

    Perhaps you want something slightly different from macropod's solution. See attached zipped workbook; it takes month and year into account. I also included a pivot table, just as an example.

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Sum using three criteria (2002)

    <P ID="edit" class=small>(Edited by macropod on 12-Dec-03 21:32. Updated to incorporate year into formula)</P>Hi Dean,

    See attached. Your workbook didn't have any formulae so that I could see where the problem was, but there were some fairly basic problems, including your defined names being in different columns than the equivalent data, and using LTIs instead of just LTI on the Stats sheet.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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