Excel Magic Trick 1083: SUMIFS: Add Invoice Amounts Between Start & End Dates (Adding For Period)

See how to Add Invoice Amounts Between Start and End Dates using the SUMIFS function, a start and ebd date in cells, the ampersand (join symbol) and comparative operators in double quotes.

[excelisfun]

Excel Magic Trick 1083: SUMIFS: Add Invoice Amounts Between Start & End Dates (Adding For Period) 

[davidgreen]

Mike - yet again you are a life saver - i have been frantically searching for information on how to use date ranges and more importantly reference cells (mine are named) and although I have found many examples of using hard coded dates, none have shown the use of reference cells.

Thank you again - the amount of beers I owe you is really starting to stack up!!!

[amansingh]

Hi Mike sir... as usual I love ur vid.. its simply amazing.. just a small.suggestion, instead of clicking at the column's top right to auto fit column... u can use the shortcut. alt + o + c + a...dats from.2003 excel.. i will confirm for 2010 and.post... u obviously wud already know it... god bless buddy

[duffry]

Maybe because I work with 2003 and 2007, or possibly because I just prefer SUMPRODUCT I would probably use:
=SUMPRODUCT((A2:A3210>=E6) * (A2:A3210<=F6) * (C2:C3210))
I find SUMIFS clunky with it operators added as strings and SUMPRODUCT easier to customise when the scope creeps.

[wcthrill]

Wondering why my SUMIFS formula doesnt work when I convert insert table on my data. Works fine until I change data to a table. Is this a glitch with 2013?

[miltonbryant]

I have tried to make a formula that will automatically populate the report due dates base on my input start date.   I have include the criteria and a sample below.  I have tried using the EOMonth and Edate formula but could not make it work with an IF statement.    I can use Excel 2010, 2013 or 2016. Any ideas would be greatly appreciated.

COR Quarterly Report/Review Tracker    
 Start Date     Report Due Q1        Report Due Q2        Report Due Q3          Report Due Q4
                         OCT-DEC                   JAN-MAR                 APR-JUNE                 JULY-SEPT
1/4/2017        5-Apr-17                      5-Jul-17                     5-Oct-17                   5-Jan-18
1/17/2017      5-May-17                    5-Aug-17                    5-Nov-17                  5-Feb-18
2/6/2017    
2/18/2017  
  
1.  All Qtrly Reports are due by the 5th of each month.  If the 5th falls on a weekend or Holiday the report is due NLT the following business day.    
2.  If Start date is issued on or before the 15th of the month then Report is due 90 days from that month.  (example:  START DATE: 6 Nov 16 - 1st review due NLT 5 Feb 17)    
3.  If NTP is issued after the 15th of the month then Report is due 90 days from the next month.  (example:  START DATE: 17 Nov 16 - 1st report due NLT 5 Mar 17)

[amansingh]

Hi Mike sir... as usual I love ur vid.. its simply amazing.. just a small.suggestion, instead of clicking at the column's top right to auto fit column... u can use the shortcut. alt + o + c + a...dats from.2003 excel.. i will confirm for 2010 and.post... u obviously wud already know it... god bless buddy