How do I change the date reference in the formula below: >=2018-1-01 and <=2018-1-31, so that it will refer to a single cell date in cell J4 correctly? The formulas below work correctly, no problem!
(E8) =SUMIFS(D$44:$D$84,$B$44:$B$84,">=2018-1-01",$B$44:$B$84,"<=2018-1-31")
(F8) =SUMIFS(D$44:$D$84,$B$44:$B$84,">=2018-2-01",$B$44:$B$84,"<=2018-2-28")
(G8) =SUMIFS(D$44:$D$84,$B$44:$B$84,">=2018-3-01",$B$44:$B$84,"<=2018-3-31")
etc.
The difficulty is that each year I have to manually go in and change every reference to the new year in the formulas, and worse, have to ask people in our volunteer group using the expense report who are not familiar with Excel to go into formulas and edit them. Not very practical on the last part! In cell J4 if I can just change the date to 2019 next year and have the formulas change, well, everybody will be able to manage that!
Adding a simple cell reference does not work, i.e.:
=SUMIFS(D$44:$D$84,$B$44:$B$84,">=(J4)-1-01",$B$44:$B$84,"<=(J4)-1-31") Other variations similar to just adding J4 reference in the formula above do not work.
Other variations such as =(DATE(YEAR(J4),MONTH(1),DAY(1))) work as a stand-alone reference, but not in the formula. (It often returns 0.)
The first part of the formula, =SUMIFS(D$44:$D$84,$B$44:$B$84,) is necessary to look up expenses from another table by the month and place them in the monthly columns as per the screen shot. So changing that part of the formula (which works fine) is not an option.
I have tried all kind of date references in the formula and if I don't get an error message, I simply get 0.
I'm using Excel 2011 (mac)
1 Answer
I think what you want is the following:
=SUMIFS(D$44:$D$84,$B$44:$B$84,">="&J4&"-1-01",$B$44:$B$84,"<="&J4&"-1-31")
Instead of using (J4)
Ref:
I have checked that it works fine on my data set, see if this helps you!
