+ Reply to Thread
Results 1 to 4 of 4

'And' nested inside 'sumif'

  1. #1
    Registered User
    Join Date
    03-27-2013
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    2

    'And' nested inside 'sumif'

    Hello, I am looking for sum help with a nested 'And' formula inside a Sumif. Here is simplified example of what I am trying to acheive.


    Interest Rate Principle $ Amount
    1.0% 1
    1.1% 2
    1.2% 3
    1.3% 4
    1.4% 5
    1.5% 6


    I am trying to sum certain principle amounts that are within a range of interest rates. For example:, When I try to sum all principle amounts that are within the range of greater than 1% interest and less than 1.3% , I will type this formula =SUMIF($H$9:$H$14,AND(">1%","<1.3%"),$I$9:$I$14) Assume that the '$' cells in the formula are where the 2 columns are located in the excel worksheet.

    My problem is I am receiving a '0' when I enter this formula when I should be receiving a '5'. I assume that since I am receiving a 0 and not an error that it is something wrong with my And formula??

    Can anyone help?

    Thank you!

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: 'And' nested inside 'sumif'

    Try it like this...

    Use cells to hold the criteria:

    A1 = 1%
    B1 = 1.3%

    =SUMIFS($I$9:$I$14,$H$9:$H$14,">"&A1,$H$9:$H$14,"<"&B1)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    03-27-2013
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: 'And' nested inside 'sumif'

    Thank you Tony that works!
    Last edited by ekirwa2; 03-28-2013 at 09:58 AM.

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: 'And' nested inside 'sumif'

    You're welcome. Thanks for the feedback!

+ 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