Question description:
This user has given permission to use the problem statement for this
blog.
I need one cell to look at a specific range in a column, figure out if the date in those cells is between two dates and if it is I need it to take the value of the adjacent cells and sum them up. I have a column with dates, next to that a column with dollar amounts. I want one cell to make a sum of the amounts associated with a specific date range. Please help.
Solved by X. U. in 30 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
09/08/2018 - 04:38
Hello, I understand that you need help in summing all the values that fall under a date range, right?
User
09/08/2018 - 04:38
yes
User
09/08/2018 - 04:39
would you like me to show you an example of what i have on this sheet here
Excelchat Expert
09/08/2018 - 04:39
Alright, I can help you with this. Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question. We also currently do not support VBA/Macro solutions.
Excelchat Expert
09/08/2018 - 04:39
Yes, an accurate example would help.
User
09/08/2018 - 04:39
ok one min
User
09/08/2018 - 04:41
what i need is "new cell" to look into A2:A5, figure out if the date is january, it cant be after or before, it must in the month, and if it is Jan to then take the adjacent cells and add all of them up
Excelchat Expert
09/08/2018 - 04:42
So it will always be January or will you be pulling something else in the future?
User
09/08/2018 - 04:44
there are technically two date columns, one has the month that my reps projected they would close an account and the other is the "actual close date"
User
09/08/2018 - 04:44
i want to be able to sum only the amounts they closed that month
Excelchat Expert
09/08/2018 - 04:44
Okay, here's what I suggest. Have a cell that will tell you what month you want to compute.
User
09/08/2018 - 04:44
meaning sometimes they project they will close in jan and then they close in march
Excelchat Expert
09/08/2018 - 04:44
Like what I did in D1.
User
09/08/2018 - 04:45
ok
Excelchat Expert
09/08/2018 - 04:46
If you type 1/1/2018 in D1, it will only sum the January data.
Excelchat Expert
09/08/2018 - 04:46
If you type 7/1/2018 in D1, it will only sum the July Data.
User
09/08/2018 - 04:47
yes but these things are also rep specifc
Excelchat Expert
09/08/2018 - 04:48
I can only provide a solution based on how you presented your data.
User
09/08/2018 - 04:48
each month will have 5 - 10 reps
Excelchat Expert
09/08/2018 - 04:48
As mentioned earlier, an accurate example would help.
User
09/08/2018 - 04:48
sorry
User
09/08/2018 - 04:48
one sec
User
09/08/2018 - 04:48
my apologies
Excelchat Expert
09/08/2018 - 04:49
Here, use E1 to type the Rep you want to add.
Excelchat Expert
09/08/2018 - 04:49
So D1 will be the month, E1 will be the rep.
User
09/08/2018 - 04:50
= )
User
09/08/2018 - 04:50
thank you thank you thank you
User
09/08/2018 - 04:51
i think thats the answer
Excelchat Expert
09/08/2018 - 04:51
You are welcome. Glad to have helped!
User
09/08/2018 - 04:51
amazing !!!
Excelchat Expert
09/08/2018 - 04:51
Feel free to download the file to use as a reference.
Excelchat Expert
09/08/2018 - 04:51
Download this file.
[Uploaded an Excel file]
Excelchat Expert
09/08/2018 - 04:52
Would there be anything else that I can help you with regards to the original question?
User
09/08/2018 - 04:53
im trying to plug it into my sheet
Excelchat Expert
09/08/2018 - 04:53
You will of course need to adjust the range and the cell references in order for this to work.
User
09/08/2018 - 04:53
i know lol
User
09/08/2018 - 04:54
doing that now
User
09/08/2018 - 04:54
k one sec, maybe i can copy it here and we do it like that ?
Excelchat Expert
09/08/2018 - 04:54
You can just upload your file and I'll apply it for you.
Excelchat Expert
09/08/2018 - 04:55
Remember to extend the session otherwise, we'll get disconnected automatically and I can't stop that.
User
09/08/2018 - 04:55
im working in sheets
Excelchat Expert
09/08/2018 - 04:55
We have 2 minutes remaining. Please extend the session or else we'll get disconnected automatically.
Excelchat Expert
09/08/2018 - 04:56
Hello?
Excelchat Expert
09/08/2018 - 04:56
Please extend the session.
Excelchat Expert
09/08/2018 - 04:56
We will get disconnected in 1 minute and I can't help you then.
Excelchat Expert
09/08/2018 - 04:57
I don't think you'll still be able to extend the session now. You missed the window.
Excelchat Expert
09/08/2018 - 04:57
I'll try to work on the sheet to the right. We won't be able to chat but I'll be able to work on it after the timer expires.
Excelchat Expert
09/08/2018 - 04:57
Reply to me there as well.
Excelchat Expert
09/08/2018 - 04:57
Please confirm.
Excelchat Expert
09/08/2018 - 04:57
20 seconds.
Excelchat Expert
09/08/2018 - 04:57
I'm trying my best to help you but I can't if you won't reply.
Excelchat Expert
09/08/2018 - 05:04
Since you changed what we already worked on, I have applied the new formula in column S.
Excelchat Expert
09/08/2018 - 05:04
I'll have to end this session now since you were not able to extend nor reply to me via the sheet.
Excelchat Expert
09/08/2018 - 05:06
If you can read this, I'll be forwarding your file to our support team so it can be sent to you along with the formula for column S. As long as the columns and ranges are the same, you can easily copy the formula to your sheets file.
Excelchat Expert
09/08/2018 - 05:06
=sumifs($E:$E,$A:$A,$R3,$D:$D,">="&$R$8,$D:$D,"<="&$S$8)
Excelchat Expert
09/08/2018 - 05:06
This is for S3.
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.
