# Thread: Formula for in between dates

1. ## Formula for in between dates

 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. 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. 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. 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
•