+ Reply to Thread
Results 1 to 4 of 4

Cannot get SUMIFS to work when using <=TODAY() or when linking to another cell

  1. #1
    Registered User
    Join Date
    03-14-2009
    Location
    Indiana
    MS-Off Ver
    Excel 2003
    Posts
    41

    Cannot get SUMIFS to work when using <=TODAY() or when linking to another cell

    I want to 1) get a current balance in an account and 2) get a balance of that account on any defined date. I'm using SUMIFS. The only way I can get it to work is if I manually enter the date. Users need to be able to see current balance without entering anything. SUMIFS should work with the TODAY() function, but it won't if I link it to another cell or whenever I use the < or <=.

    Spreadsheet attached. On the Funds Available worksheet, I have identified in green what I want to work.
    Attached Files Attached Files

  2. #2
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Cannot get SUMIFS to work when using <=TODAY() or when linking to another cell

    =SUMIF('Fund Deposits'!A:A,"<="&TODAY(),'Fund Deposits'!B:B)-SUMIF(Expenditures!A:A,"<="&TODAY(),Expenditures!B:B)
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    03-14-2009
    Location
    Indiana
    MS-Off Ver
    Excel 2003
    Posts
    41

    Re: Cannot get SUMIFS to work when using <=TODAY() or when linking to another cell

    WOW! This works. I entered it and got the number. But when I apply the same concept to linking to a given/specified date, it falls apart. Thoughts?

    Also, how do I/where do I search for these idiosyncratic formula applications? I am actually using Excel 2010 now, but could not find this solution in the Excel 2010 manuals.

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

    Re: Cannot get SUMIFS to work when using <=TODAY() or when linking to another cell

    Quote Originally Posted by HarvardMajesty View Post
    when I apply the same concept to linking to a given/specified date, it falls apart. Thoughts?
    Can you show us that formula?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ 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