Excel - How to SUMIF Between Two Dates in a Range - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

Hi, I'm making myself a weekly budget spreadsheet. I've made a sheet with all my monthly outgoings, and I've worked out how to use SUMIF to combine the amounts for each day, but I want to include a reference and don't know how to combine text cells based on a specific date reference... does that make sense?
Solved by E. U. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 16/04/2018 - 09:27
Hello! How are you?
User 16/04/2018 - 09:27
Hey, I'm alright just struggling.
Excelchat Expert 16/04/2018 - 09:28
It's alright, I'm here to help you
Excelchat Expert 16/04/2018 - 09:28
Are you able to provide a sample data that we could work on?
User 16/04/2018 - 09:29
OK, give me a second
Excelchat Expert 16/04/2018 - 09:31
Sure, take your time. Please let me know once you're done
User 16/04/2018 - 09:37
OK so basically, I have certain things that go out on a monthly basis and certain things that go out on a weekly or bi-weekly basis and find it hard to keep track of both. Is there a way I can list monthly and weekly outgoings as I have and then combine the references and amounts on a week by week basis?
Excelchat Expert 16/04/2018 - 09:38
And this will be reflected in your Wk Beg 16 April, tab, correct?
User 16/04/2018 - 09:39
User 16/04/2018 - 09:39
And then I could create a new tab for each week and the cells would automatically update.
Excelchat Expert 16/04/2018 - 09:39
also to confirm, what do you intend to put under column C, Outgoing Reference? Is it a multiple or single value?
User 16/04/2018 - 09:40
So it would say "Internet", or "Rent/Water Bill"
Excelchat Expert 16/04/2018 - 09:41
no Cleaner or Gardener (this is with reference on the weekly outgoing)
User 16/04/2018 - 09:41
That too
User 16/04/2018 - 09:42
So it would combine the weekly and monthly amounts and tell me what references they are
Excelchat Expert 16/04/2018 - 09:43
okay, so that is what you meant by combining text based on a date
User 16/04/2018 - 09:44
Excelchat Expert 16/04/2018 - 09:45
Are you amenable to changing the Day under Weekly Outgoings and Date under Monthly Outgoings to format m/dd/yy?
Excelchat Expert 16/04/2018 - 09:45
we could always apply formatting to this cell to show only the day number or name afterwards
User 16/04/2018 - 09:48
Well they dont refer to specific dates, I know that on the 17th of every month is my rent and water bill, and every other Monday my cleaner come. Surely if they're m/dd/yy then this will change every week?
Excelchat Expert 16/04/2018 - 09:48
I see, that makes sense
Excelchat Expert 16/04/2018 - 09:49
how about adding a column in Wk Beg 16 April to show only the day number (which you can opt to hide)?
User 16/04/2018 - 09:49
Yeah that could work?
Excelchat Expert 16/04/2018 - 09:49
Yup, for a while
Excelchat Expert 16/04/2018 - 09:50
I'll try to do this on my sheet offline first, is that okay?
User 16/04/2018 - 09:50
Excelchat Expert 16/04/2018 - 09:51
This may take a little while so I'll thank you for your patience in advance :(
Excelchat Expert 16/04/2018 - 09:51
User 16/04/2018 - 09:52
Thank you
Excelchat Expert 16/04/2018 - 10:03
Hi! Please see attached
Excelchat Expert 16/04/2018 - 10:03
[Uploaded an Excel file]
Excelchat Expert 16/04/2018 - 10:04
May I know which excel you are running in?
User 16/04/2018 - 10:07
I'm on a mac so was using numbers
Excelchat Expert 16/04/2018 - 10:07
Could you please confirm if this is working?
User 16/04/2018 - 10:07
Downloading excel now though
User 16/04/2018 - 10:07
give me a min
Excelchat Expert 16/04/2018 - 10:07
This might be applicable to Excel 2016 though
User 16/04/2018 - 10:09
I'm downloading now so let me see
Excelchat Expert 16/04/2018 - 10:09
Okay :)
Excelchat Expert 16/04/2018 - 10:13
Apparently it also works on the Google Sheet
Excelchat Expert 16/04/2018 - 10:13
if you have time, you may also want to check the document preview
User 16/04/2018 - 10:14
I can see
Excelchat Expert 16/04/2018 - 10:14
I hope this solves you problem
User 16/04/2018 - 10:14
Things aren't updating correctly
User 16/04/2018 - 10:14
Gardener shouldn't be there for monday
Excelchat Expert 16/04/2018 - 10:15
thanks for pointing that out
Excelchat Expert 16/04/2018 - 10:15
let me check one sec
User 16/04/2018 - 10:15
Internet should be 19
User 16/04/2018 - 10:15
Gas should be 21
User 16/04/2018 - 10:16
Also, I have week A and B because I need to have cleaner one week on a monday and gardener the next on a sunday
Excelchat Expert 16/04/2018 - 10:17
Please see attached
[Uploaded an Excel file]
Excelchat Expert 16/04/2018 - 10:17
i have also updated the document preview, for your quick reference
User 16/04/2018 - 10:19
User 16/04/2018 - 10:20
Howcome it's not updating if I add things?
Excelchat Expert 16/04/2018 - 10:20
You're welcome
User 16/04/2018 - 10:20
And it should only show cleaner or gardener depending on if it's week a or b
User 16/04/2018 - 10:20
See I added gas on monthly
User 16/04/2018 - 10:20
It should be on 21
Excelchat Expert 16/04/2018 - 10:21
Oh, yes... let me check again
Excelchat Expert 16/04/2018 - 10:22
found it, i failed to fix the first row reference so it excluded it on the search when i copy and paste the formula down the rows
Excelchat Expert 16/04/2018 - 10:23
By the way, how do i know if Wk Beg 16 April is week A or B?
User 16/04/2018 - 10:24
It says Week B in cell A1
Excelchat Expert 16/04/2018 - 10:25
Thanks for pointing that out
Excelchat Expert 16/04/2018 - 10:25
is it okay if we add another column to indicate week A or B?
User 16/04/2018 - 10:25
Excelchat Expert 16/04/2018 - 10:25
this again, you can opt to hide like the day number
User 16/04/2018 - 10:26

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc