Results 1 to 8 of 8

Thread: SUMIF Function

  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    New York
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SUMIF Function

    The SUMIF function adds a range of data if another range of data satisfies a truth condition. Is there a way to use a relative reference or a formula to define the truth condition rather than an input value?

  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF Function

    Sure:

    =SUMIF(B1:B12,E1,A1:A12)

    This sums all values in A:A12 where B1:B12 is equal to the value in E1.

    =SUMIF(B1:B12,">" & E1,A1:A12)

    This sums all values in A:A12 where B1:B12 is greater than the value in E1.

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

    Re: SUMIF Function

    Yes, within limits. For example you can't use a boolean formula dynamically; sumif will just match against the formula result, and so will sum only values which are true, or false, or 1, or zero.

    You can use sumif directly where the external reference is a straight match; where cell a1 contains UT, the comparison range contains US States, and the data range is numbers, =sumif(comparison_range,a1,data_range) will sum all numbers for UT.

    And you can use sumif with externally referenced arguments using a little string formula to create a boolean; where cell a1 contains a variable to search against, you can use
    =sumif(comparison_range,"<"&a1,data_range)

    Care to post an example of what you want?

    The SUMIF function adds a range of data if data within that range or a corresponding range satisfies a condition.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    eastern Connecticut, Connecticut, USA
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF Function

    Another highly flexible approach is to use array formulae constructed in the form of:
    =Sum(If(CompareRange operator condition),If(CompareRange2 operator condition2),Sumrange,0),0)).
    This example would 'AND' the conditions. You could add the two 'IF' statements together to 'OR' the conditions. Look under TOOLS/Wizard for the Conditional Sum wizard. Play with this and see what formulae it builds for different circumstances and you should be able to get a good idea of how this works.
    HTH,

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

    Re: SUMIF Function

    Very cool, Gene, even if you are getting off the original =sumif() topic.[img]/w3timages/icons/wink.gif[/img]

    After messing around with them, I tend to stay away from array formulas on very large arrays because of their capacity to slow recalc. For a query this complex, I go to the =dfunction(data,offset,criteria) family.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    eastern Connecticut, Connecticut, USA
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF Function

    off the topic????

    I always thought SUM(IF was the same as SUMIF -- just smarter[img]/w3timages/icons/clever.gif[/img]

  7. #7
    New Lounger
    Join Date
    Jan 2001
    Location
    New York
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF Function

    Thanks for your help. You have solved my problem.

  8. #8
    New Lounger
    Join Date
    Jan 2001
    Location
    New York
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF Function

    Thank you for your help. You have solved my problem.

Posting Permissions

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