+ Reply to Thread
Results 1 to 8 of 8

Using SUMIF across more than one tab and with date ranges as a criteria.

  1. #1
    Registered User
    Join Date
    10-03-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    4

    Smile Using SUMIF across more than one tab and with date ranges as a criteria.

    Hi - thanks in advance for our help, I've searched everywhere!

    Basically I need a cell to SUMIF to a different tab/sheet - I'm basically saying "If an invoice was dated 01/01/2012 to 31/01/2012 then add up those invoices' net value"

    Here's a link to the file: https://docs.google.com/open?id=0B9i...VNOZlMyMnp1Nnc (Couldn't get the uploader to work)

    The selection I have highlighted in the 'P & L' tab is where we're looking, it then calls on the 'Purchase Ledger' and 'Sales Ledger' tabs to complete the fields - so eventually I'l be able to establish (not finished the doc yet) Profit and Loss by month.

    Hope this is clear - thanks in advance!

    David

  2. #2
    Registered User
    Join Date
    10-03-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Using SUMIF across more than one tab and with date ranges as a criteria.

    Oh yes! in the google link, click FILE, then DOWNLOAD - Then you can open in Excel.

    Ta!

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,901

    Re: Using SUMIF across more than one tab and with date ranges as a criteria.

    To Attach a File:

    1. Click on Go Advanced
    2. In the frame Attach Files you will see the button Manage Attachments
    3. Click the button.
    4. A new window will open titled Manage Attachments - Excel Forum.
    5. Click the Browse... button to locate your file for uploading.
    6. This will open a new window File Upload.
    7. Once you have located the file to upload click the Open button. This window will close.
    8. You are now back in the Manage Attachments - Excel Forum window.
    9. Click the Upload button and wait until the file has uploaded.
    10. Close the window and then click Submit.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    10-03-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    4

    Wink Re: Using SUMIF across more than one tab and with date ranges as a criteria.

    Hi - yes thanks for that info on how to upload but where I'm working uploads are blocked, I had the file saved in google docs already... hence the link.

    Anyone able to have a look for me please?

    Thanks
    Dave

  5. #5
    Registered User
    Join Date
    10-04-2012
    Location
    Stockholm
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Using SUMIF across more than one tab and with date ranges as a criteria.

    I have exactly the same problem! The problem seams to be that you can't have multiple criterias within the SUMIF, or at least not use boolean functions. I would like to use AND for entering the criteria "date smaller than x" AND "date larger than y". I hope someone can answer.
    BR,
    Helena

  6. #6
    Registered User
    Join Date
    10-04-2012
    Location
    Stockholm
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Using SUMIF across more than one tab and with date ranges as a criteria.

    Hi again,
    If you can squeeze your data into two columns (one for date and one for the relevant data) I have a solution!
    Use SUMIFS. Put all dates in column A and all the data in column B in one worksheet, let's call it MyData and let's pretend you have 10 rows of data (doesn't matter if some rows are some other text, those will just be skipped).
    Then open another worksheet and write your criteria in column A and B. Let's say you want to sum all data from dates 17 september to 23 september. Write
    >=2012-19-17 in column A
    <=2012-09-23 in column B (or whatever date format you prefer)
    Then you can write in column C SUMIFS(MyData(B1:B10); condition 1(reference to column A on this row); MyData(A1:A10); condition 2(reference to column B on this row); MyData(A1:A10))
    This will sum all numbers concearning those dates!

    If you have english Excel all ";" above should be "," (I have swedish Excel :-( ) And all the references should be written by marking in your Excel sheet, I can't write the exact syntax now since I'm not at work by my sheet.

    All the best,
    Helena

  7. #7
    Registered User
    Join Date
    10-03-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Using SUMIF across more than one tab and with date ranges as a criteria.

    Hi ya - thanks for this, although I do need my data on separate sheets

  8. #8
    Registered User
    Join Date
    10-04-2012
    Location
    Stockholm
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Using SUMIF across more than one tab and with date ranges as a criteria.

    Hi again,
    Well I haven't seen your sheet, but I guess you could mix sheets in your criteria. I think the only requirement is that the criteria array and the data array have the same length. I don't remember how many criterias+data you can put in one SUMIFS but I think I have seen 64. That should be enough even if you nest them?
    Good luck!
    /Helena

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

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