3

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)

ScreenShot with Notes and Formulas

0

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: https://support.office.com/en-us/article/calculation-operators-and-precedence-in-excel-48be406d-4975-4d31-b2b8-7af9e0e2878a#tblID0EABBAFAAA

I have checked that it works fine on my data set, see if this helps you!

enter image description here

  • Side note with J4 (the year), if your excel needs to be edit each year. It might be beneficial to use this "=YEAR(TODAY())" for your J4. So there is no need to ask anybody to change the J4 each year~ – Ng Sek Long Feb 19 '18 at 3:29
  • oooh, i was very hopeful! I hadn't tried that formulation. Unfortunately, I'm getting "0" as a return. I already know that my original formula adds up the numbers and the answer is 72. (You'd have to have the info in the list to get the same answer.) Is there a cell formatting setting I'm missing? On your side note, I thought about that, but when using =Year (Today()) it will automatically return "0" when the new year comes in the columns, as the new year will then be 2019, and the data that is under 2018 will not meet the criteria. – GaryM Feb 19 '18 at 4:05
  • Can you give me some example row and expected outcome? So I have something more to test on. Other possibility is that, maybe mac 2011 doesn't support the & syntax. – Ng Sek Long Feb 19 '18 at 4:57
  • Sure. Very kind of you to look. Perhaps the best thing is to just upload the spreadsheet if you are OK with that. I'll put some notes in it to help. Let me see if I can figure out how to upload.... – GaryM Feb 19 '18 at 18:57
  • 1
    YES! Thank you! It works! There was one key element, however, because at first it did not work. The FORMAT of the date. I noticed you had "general" as the format. I was using a "Date" format. As soon as I changed it to "general", it worked!! Thank you! (I checked the green checkmark, is that correct?) – GaryM Feb 20 '18 at 4:15

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.