Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    sumif with cell value as criterion (2003)

    A formula like
    =SUMIF(savings,"<=1000",savings)
    works ok.
    However, =SUMIF(savings,"<=B41",savings) doesn't.
    Nor does =SUMIF(savings,"<=(value(b41))",savings)
    What's the syntax to generate what I'm after, please?

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: sumif with cell value as criterion (2003)

    Hi John

    Try

    =SUMIF(savings,"="&B41)
    Jerry

  3. #3
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sumif with cell value as criterion (2003)

    Yep, that does the trick, Jezza.
    Should I have known this?
    How?
    With the equivalent COUNTIF formula, like this
    =COUNTIF(savings,"<="&B41),
    Excel puts the quotes and ampersand in itself, when the formula is constructed in the build-a-formula dialogue box.
    Thanks again

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

    Re: sumif with cell value as criterion (2003)

    It's important to keep in mind that the second argument of SUMIF and COUNTIF - the condition - is a text string that is interpreted literally. If you use
    <code>
    =COUNTIF(savings,"<=B41")
    </code>
    Excel will compare the value if each cell in the savings range to the text string B41, and count it if the value is the same or before it in dictionary order. If you use
    <code>
    =COUNTIF(savings,"<="&B41)
    </code>
    Excel will first evaluate the expression "<="&B41. Say that B41 contains the value 37, then the expression evaluates to "<=37". So Excel will compare the value if each cell in the savings range to 37, and count it if the value is less than or equal to 37.

Posting Permissions

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