How to Sum for a Specific Date Range in Excel
ฝัง
- เผยแพร่เมื่อ 2 ก.ค. 2024
- In Excel, you can use the SUM function to calculate a simple total for a range of cells.
If you want a total based on conditions, such as dates between a specific start date and end date, you can use the SUMIFS function.
The SUMIFS function will show a total based on one or more criteria.
Watch this short video to see the steps, and then verify that the total is correct.
Visit my Contextures website to download the sample file, and to see more ways to SUM in Excel.
www.contextures.com/xlfunctio...
Instructor: Debra Dalgleish, Contextures Inc.
More Excel Tips and Tutorials: www.contextures.com/tiptech.html
Subscribe to Contextures TH-cam: th-cam.com/users/contextu...
Video Timeline
0:00 Introduction
0:45 Start SUMIFS formula
1:04 Formula Criteria - Start Date
1:52 Formula Criteria - End Date
2:25 Test the Formula Result
2:46 Get the Sample File
VIDEO TRANSCRIPT
One of the most common things you do in Excel is get a total of something. So here we have a column where we've sold items on certain dates and we can see how many units we sold each day. We have a total at the bottom, and that just uses the SUM function.
If you want to put some conditions on the total, instead of using SUM, you could use a SUMIFS function. This is available in Excel 2007 and later versions.
In this cell I'd like to see the total for a specific date range. I've put a starting date here and an ending date.
In this cell I'm going to put in a SUMIFS formula, and it will look at the dates and then only total the units that are between the start and end date.
To start the formula, I'll click in this cell, type an equal sign and the SUMIFS function, and open bracket.
The first thing we have to put in is the sum range, so which cells do we want to add up if we meet the criteria? We want to add these units sold. Put that in, and put in a comma.
The next thing is the criteria range. Which cells do we want to check to see if they meet the criteria that we've set. And that's the date cells. I'll select those. Type a comma.
The next thing is the first criteria. We want it to be based on this start date. We want any numbers that are for a date on or after this start date.
I'm going to put the operator in, and the operator will be greater than or equal to, and I have to put that inside quotes. Double quote, greater than, equal to, double quote.
I'm going to join that to a cell reference. I'll put in an ampersand and then click on the start cell, so it's going to look for any items where the date is on or after that start date.
Then we're going to have to put in our end date. For the criteria range 2, again, we want it to check those dates, type a comma. The second criteria is this end date. We want things that are on or before that.
Again, the operator will go in double quotes, and this will be less than, equal to, double quote, and we'll join with the ampersand.
Click on the end date, and close the bracket. Press Enter.
We can see that there were 494 units sold in this date range.
To verify that quickly, we can manually select the dates in that date range. The first one within that date range is January 9th going down to February 4th.
When I look in the status bar, the sum is 494, which matches our total. So with SUMIFS you can use multiple criteria to get a total.
For more Excel tips and tutorials and to download the sample file from this video, please visit my Contextures website at www.contextures.com - วิทยาศาสตร์และเทคโนโลยี
![ภาพนี้ก็ฮาเหมือนกันนะเนี้ย #1 SS8 [ พากย์นรก MEME.EXE ] | easy boy](http://i.ytimg.com/vi/Ytc50m91QK8/mqdefault.jpg)
video from 11 years ago saved my job, thank you!
You're welcome, Dimas, and thanks for letting me know that this video helped you!
@@contextures I love the fact that you even replied to my comment 🥲, wish you the best for anything you do! ❤
Great video. Simple and to the point. Worked perfectly! Thanks
Oh gosh. at last. No word could describe my gratitude. Thanks loads.
I don't even have the words for how awesome your channel is. Thank you so much. You make me seem like an EXCEL guru.
Thank you!
Thanks very much for this! I had a table of expenses by week that I needed to easily total by month to create data for a table. Exactly what I was looking for!
Thank you SO much! this was one of the best tutorials.
All of your videos are of great help - can't thank you enough.
Thank you! I'm glad you find the videos helpful
Extremely helpful! Thanks for such a wonderful tutorial :)
Truly brilliant! I had been using filters before, which is very clunky. The presentation was excellent also, easy to follow and understand!
Thanks, Andrew! I'm glad you liked the video.
Your clarity is amazing.Talented teacher welldone!
Thank you very much!
Thank you so much! Been looking for this. I already subscribed as my way of thanking you. 😊
Super helpful and exactly what I needed!
Very short and precise ,exactly what I was looking for ,Thanks
Thanks! I'm glad it helped you
This was the best! I got the info I needed within the first 5 minutes. Many thanks!
You're welcome, Mark! Thank you for letting me know that it helped
Thank you. Works perfectly!
thanks alot mam ,finally got it ,searching this for a long time😍
You're welcome Sahad, and thanks for letting me know the video had what you were looking for!
Just what I needed (works well with COUNTIFS, too). Thank you very much.
You're welcome, Frank! Thanks for your comment
Thank you so very much for this tutorial! I have been trying to find out how to do this formula for a long time and you have explained it very simply, step by step, so I was able to put in the correct formula, and it works! So pleased!!! Thank you!!! :-)
You're welcome, Janet, and thanks for your lovely comment - I appreciate it!
Thank you very much, just what I was looking for
Thank you so much for the helpful information
You're welcome, Talal, and thanks for your comment!
i was searching for it
thank you so much
Nice perfect, exactly what I was looking for.. Thanks
+Mark Nelson You're welcome! Thanks for letting me know that the video helped.
you saved me lot of time...... after so many hit and trials
thanks a lot
This Helped so much! Thank you for your video!
You just made something I am working on so much easier.
Thanks, Ugo, and that's great to hear!
This is a very helpful video. Super helpful.
Thank you very much :)
You save my 1 hours thank you so much
You're welcome, and I'm glad the video helped save your time!
-Debra
Super helpful, thank you!!
Wow Thank you for the video.
Amazing video! Thank you.. :) I struggled a little bit getting it to work, but found out it was due to the fact that my date cells were merged - after that it worked like a charm.. :)
You''re welcome! Thanks for letting me know how you fixed the formula when it wasn't working.
Perfect! Thank you very much!
Very useful even after 8 years. Thanks!
You're welcome, Poojitha, and thanks for letting me know that the video was useful to you, after so many years!
@@contextures Glad that you saw my response. 😊
Very helpful video
Thank u
I've been loosing my mind trying to find out how to solve this, and in a way I can understand - Thank you!
Ahh hah, very cool. I've been trying to get this done for about a week now with no success! Your vid is intuative and easy to follow.Worked like a charm, well Done and thx again...
Thank you, David! Glad the video helped you get your work done
Thank you so much. This really helped alot.
Nice. You kept it easy!!!. Thank You.
Thank you!
Thanks really helpful
Thanks very useful.
amazing!!!! love this share! Thanks!
Thanks, I'm glad you liked the video!
3 hours of failing, and then you save me in less than 3 minutes. Thank you!
Thank you so much!
You're welcome, and thanks for your comment!
Thanks A lot for saving my time
You're welcome, Pradeep, and thanks for your comment!
It's really helps, Thanks from AUG 2021... 🙏🙂
You're welcome, Kaushal, and thanks for your comment!
Thank You - this helped me lot
You're welcome, Sagar! Thank you for letting me know that the video helped
Thanks Debra
So helpful, regards
Excellent Video
Thank you, Charles!
very neat explanation.....
Thank you, Mohammed!
Thanks, Nice tutorial
You're welcome, Enamul, and thanks for your comment!
@Elisabeth, what do you see in the status bar, if you select the cells that have the values you want to sum? Do you see a sum in the status bar, or just a count?
Thanks a lot.
Perfect for a problem I had
you are great ........................
Thank you, I love you!
+onebombcutie You're welcome!
+onebombcutie You're welcome!
Wow!! It's so simple once you explain it. I tried the same formula except for the quotes and ampersand. In other words, I was nowhere near the correct formula. Thank you so much for your expertise. :)
You're welcome, Ellen, and thanks for letting me know that the video helped you!
thankyou so much
Very nice
Nice trick.
Hats off to you...
Thank you!
love ya! thx!
You're welcome, Ayan, and thanks for your comment!
Short and to the point. What if I have date & time in two different columns and want to add between a two different date-time combinations...what would that formula look like?
Thank you very much
You're welcome, Sora! Thanks for your comment.
Tq soo much mam, this helped me alot
You're welcome, Sonia, and thanks for your comment!
thankx u shared great
THANK YOU...
Thank you
You're welcome, and thanks for your comment!
Thanks, I couldn't find that anywhere
You're welcome, and thanks for letting me know that it helped you
nice vedio ,thanks,
You're welcome, Carmel, and thanks for your comment!
THANK YOU SUPER
You're welcome, and thanks for your comment!
This is fantastic... can you tell me how to make this formula for show error if sum range is empty. because this formula suppose blank cell as 0. i want error showing message on blank cell
@contextures inc
This is amazing, What would be perfect if this could be I cooperated in to a pivot table, so the table returns a sum of the values i.e. products sold(£) during one particular date range. Any idea Debra?
Very helpful and easy follow video! Is there a version of it that would look up 2 criteria? i.e. look of values within a date range for a specific product type
Thanks Emily, and we did another SUMIFS video recently, with text criteria. Maybe it will help you. th-cam.com/video/hHHKElZsLAU/w-d-xo.html
Can you explain through same with sumofproduct formula
Excellent , sir, u have done excellent job i appreciate n expected more from yr side to teach us that v can learn more n more ....... thnx, yr version is old of 2013 , pls describe latest version of 2018
It would be the same in the latest version of Excel.
what if you have multiple rows of data relating to each sepcific date in a list, i.e. units sold, cost of goods sold, interest pmts and so on and you want to copy the formula over? Does the dates have to be right next to the column (or row) you want to sum up? great video btw and thanks for any answer!
Obrigado.
Its great....
Thank you!
Thanks!!!
You're welcome! Thank you for watching my video
Hi just wondering if this formula works also if I want to get the sum in two colums.
What about in the event the event where the date range cells are empty? Is there a way to pull the full sum if no dates are provided (instead of getting a null value)?
Nice video! Come in handy. I have a question. Could I input the empty cells (in which cells data will be input in future) in both of criteria range (maybe it will long till last row of excel) for automatic calculation?
And also could I define start & end as same for each day calculation method?
Thank u.
hi thank you great formula. if i want to write down the date in the formula instead of choosing on other cell, what the magic word after "&" ? thank you
Thanks, and for a date, use DATEVALUE("3/1/17") or DATE(2017,8,1)
A note to others. I kept receiving a zero from this formula. Turns out that my dates were in text format. I used Data>Text to columns to force them to become dates, and everything worked after that. I hope that helps someone.
hey plz can u show me how to see the end of day sale total (daily sale) after every transaction is done should be saved at some column . tnxxxx
Only month criteria can we sum the table.... I am not able to solve.
is there a way to use purely function notation?
Some how , how I ll know that on this article the formula have to apply, or how v can use the different formula that ll be prefect... n works properly.....
How do I collect amount and dates from several sheets? I have 33 sheets, and need a week of sales tottal.
Can you help me, I want start and end date like just month January start and end should February.
thnx
Hi, may I know how to count the selected cells within the period covered. Sample: I only select (E190, E205, E212 within May 4 to 10, 2021 only) thanks
What if my units were names? Using this same formula im getting back 0. All suggestions appreciated.
Thanks,
What if I want the product of cells that fall within a date range?
How to sum if dates in rowes landscape
and how to make this in vb?
Hello! I loved your tutorial, it's very helpful. To be honest, this is the closest solution to my problem now. But I have a question and I'm hoping you have an answer. For example, I have separated the Due Dates by conditionally highlighting them between, less than 30 days, between 30-60 days and 61 days above, is there a formula where the total amounts can be totaled?
For example, I need the total amount to be paid of vendors who's Due Date passed less than 30 days?
My boss wanted me to have this and your kind response will he highly appreciated. I hope you can help me. Thank you 🙏🏻
Thanks, John, and you could use 3 COUNTIFS formulas for that. Check for invoices with no Paid date, then check if the Due date is in one of your date ranges. For example:
=COUNTIFS(Paid,"",Due,">="&TODAY()-29,Due,"="&TODAY()-60,Due,"
but how can i do it between 2 times for =sum hourly data??
this is the first logical tutorial I've seen for this. Unfortunately its still not working for me. Its coming up with the #value! which i always interpreted as there are no values to add. However even when i put in values that should work, nothing comes up
I named the ranges so that I could use it as numbers get added (as a forever growing list) could this be part of the problem? does every cell need to have a value in order for the formula to work?
Thanks for any ideas!
This formula is too helps me. More over can you tell how to get sum in category wise. Example. In this table add category a,b,c like. Need total sum of a=? , B=? , C=? Like. Can you help me. Because I want to make a my own food budget table