Results 1 to 6 of 6
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    using SUMIF (xl97/xl2000)

    Hi all,
    I need to determine a total for a given cell where the criteria comes from a selection made in other cells. Clear?? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    Example: In English, the formula for C11 would be:

    C11 = total of C3:C7 where B3:B7 = "Dog"

    I have five items which need to be totaled for a period of 60 months. In the above example, "Dog" can be listed 0 or 5 times (and so can any of the other 4 items).

    I attached a sample worksheet to make it more clear what I'm trying to do because I'm not quite able to explain it well enough in words. Maybe SUMIF isn't the right formula to use, but I'm not too familar with anything or than the trivial formulas.

    Any thoughts would be appreciated.
    Deb <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: using SUMIF (xl97/xl2000)

    =SUMIF(b3:b7,"=Dog",c3:c7)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: using SUMIF (xl97/xl2000)

    Wow, what a response, I barely clicked "Post It" when you had the answer. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> You probably didn't even have to look at my sample,either.

    I really gotta sit down one day soon and read Walkenbach's Excel2000 formula book....

    Thnx again, Deb <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: using SUMIF (xl97/xl2000)

    Yeah, excellent timing, wasn't it <bg>.

    And you were right, I did not read the attachment, nor did I test the formula I suggested <g>.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: using SUMIF (xl97/xl2000)

    I have had questions asked, usually by auditors, as to how do you know the answer is correct? Where i anticipate this may happen I take one of two approaches, which helps this vexed event.
    1. use advanced filter to select only the records fitting the criteria, and have these copied to another part of the w/s, then sum.
    2. Use an "if" formula to extract the number using criteria in a fixed cell, then summing the whole column.
    Jan's formula is accurate and concise, but when you need to "prove" the answer, especially in a w/s with lots of data, then the above can be a quick way to provide the information without changes to the base data set.
    <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15><img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: using SUMIF (xl97/xl2000)

    Good point.

    A good spreadsheet habit is to build check formulas into your work, such as summing the sumifs and compare them to another formula which should result in the same answer. I often do this just to make sure I get it right in the first place, then leave the checks in the WB as evidence. An added benefit of this habit is that if you then perform some modifications and blow something subtle up, the cross-checks may warn you. Very few WB authors create this kind of self-checking.

    And you can show the self-checks to the auditors.

    Or, since I used to be an auditor (before dirt was created), tell them quite seriously "that's your job". <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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