Results 1 to 4 of 4

Thread: Formula for in between dates

  1. #1

    Formula for in between dates



    Register for a FREE account, and/
    or Log in to avoid these ads!


    Account
    Date Vendor Vendor Name Description Amount Paid
    2720512 7/7/2014 18 TPS-Treasury KTC - Shadow Day - Rogers High School $ 253.00
    2213580 7/21/2014 80007 DeAnn Cooks College Access CGP Summer Institute $ 231.43
    2199682S 7/30/2014 293 Admiral Express Stamp/Supplies $ 19.99
    2213580 9/11/2014 80007 DeAnn Cooks Travel Mileage - Day of Caring and Counselor's Fair $ 25.20
    2199682S 10/16/2014 551 LU National Panhellenic Counsel LU Homecoming Step Show Admission $ 215.00
    2199682F 10/16/2014 80007 DeAnn Cooks KTC Family Night $ 90.83


    In this spreadsheet, I'm attempting to sum by a month date range and account number so my end results would be something like this:
    KTC Operating Expenses Jul-14 Aug-14 Sep-14 Oct-14 Nov-14 Dec-14 Jan-15 Feb-15 Mar-15 Apr-15 May-15 Jun-15 Total

    I'm using the SUMIFS function to look at the original spreadsheet that looks like this:

    =SUMIFS(DETAILS!F3:F19, DETAILS!B3:B19,"<=07/31/2015",DETAILS!A3:A19,"=2720512")
    (Pulling from the detail invoices spreadsheet)

    What I need to do is to be able to update the date for each month, (July totals, Aug totals, Sept, Etc.) but when I use <08/30/2014, etc it pulls July and August in my monthly report and I want the report by month. Can you help?
    Thank you for any help.
    Andrea

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    You would need something like:

    =SUMIFS(DETAILS!F3:F19, DETAILS!B3:B19,">=07/01/2015",DETAILS!B3:B19,"<=07/31/2015",DETAILS!A3:A19,"=2720512")

    or, assuming your headers are actual 1st of the month dates, formatted as MMM-YY, starting in C1 then you can use those in the formula

    =SUMIFS(DETAILS!F3:F19, DETAILS!B3:B19,">="&C$1,DETAILS!B3:B19,"<="&EOMONTH(C$1,0),DETAILS!A3:A19,"=2720512") copied across.

    and if the 2720512 corresponds to a value in the KTC or Operating Expenses column, you can use that reference so you can further copy down this formula:

    =SUMIFS(DETAILS!F3:F19, DETAILS!B3:B19,">="&C$1,DETAILS!B3:B19,"<="&EOMONTH(C$1,0),DETAILS!A3:A19,$B2)

    where B2 contains 2720512...


  3. #3
    OK, this worked great! Thanks.

    Now, what i need to be able to do is, on the details tabs, as I add invoices, i need the formulas to dynamically update so that I don't have to go in every time and change my cell ranges in the sumifs formulas. So like the range now is f3 thru F19 but as I add invoices, that will increase.

    is that something that can happen dynamically?

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    Go to the Details tab, and then go to Formulas, then Define Name.

    Enter a Name for the range, e.g. Details

    Then enter formula for source: =OFFSET(DETAILS!$A$1,,,COUNTA(DETAILS!$A:$A),6) where 6 is number of columns to include (from A to F = 6 columns).

    Now change formula to:

    =SUMIFS(INDEX(Details,0,6), INDEX(Details,0,2),">="&C$1,INDEX(Details,0,2),"<="&EOMONTH(C$1,0),INDEX(Details,0,1),$B2)


Posting Permissions

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