Today is my kids’ (we have twins – boy & girl) 3rd birthday. As we celebrate the day, I want to take a moment away from all Excel stuff and share my experience being a dad. Grab a cup of coffee or whatever keeps you going, relax and read on.
The first shock
My first shock of being a dad arrived even before my kids were born. It was the first ultrasound scan Jo (my wife) went thru. The nurse announced,
I hear two heart-beats. Let me re-check
It would not be an understatement to say Jo and I skipped a few heart-beats hearing that! We never thought we would be blessed with twins. While we are doubly happy we also worried whether we can be good parents.
To top it, when they turned up we realized that we have one of each – Nakshatra (girl) and Nishanth entered our world on September 24th, 2009.
(Note: In India parents are not allowed to know the sex of baby before they were born, hence the surprise)
My 6 Daddy years…
A quick TODAY()-DATE(2009,9,24) tells you that it has been only 3 years since my kids were born. But I have amassed 6 daddy years, thanks to raising twins!
In these 6 years, there were a handful occasions when I felt like sending a classified ad to newspapers to this effect:
Take one or both. 2 lovely kids, well raised but naughty. Potty trained and can brush their teeth. Hurry up!
But better sense prevailed and we kept them 😀
To be honest, they are not all that mischievous. But sometimes they get on our nerves so bad.
A dinner to remember
After spending probably zillion nights eating at home, one day, my wife declared, “Honey, lets go out tonight.” We usually leave the kids with our in-laws (they live close by) when we do this. But that day we must have been possessed by cruel demons. Why else would I say, “Lets take the kids along!”
And all 4 of us got in to a car and went. We did a bit of shopping (getting new clothes for kids for their upcoming birthday) and reached the restaurant. Being weekend it was full. So I left Jo & kids at lobby and went inside to book a table.
Let me break this story at this point and give you one basic rule of parenting.
You need 100% attention of one adult per one children when they are in public places.
Coming back to the lobby, as I walked inside, suddenly Nishanth and Nakshatra felt liberated & started running like incredible hulk after breaking free from underground army prison. With in a span of 30 seconds, Nakshatra walked to a near-by mini pond and sat on the ledge. As Jo managed to lug Nishanth from the other corner to that pond, Nakshatra made a backward move with a swiftness rarely seen outside tom and jerry cartoons.
SPALSH!
She is drowned up side down and started crying aloud.
Thankfully, Jo quickly pulled her out. But now all her clothes are wet and she is shivering a lot.
Fortunately, we had new clothes in the car. So I took her to the car and changed. By the time I got back, Jo already got us seats and ordered something. So we started eating. And then, Nakshatra wanted to pee. So Jo took her to the rest-room. And then Nishanth started running behind them. So I followed him.
Meanwhile a clever waiter noticed an empty table with no food items. He promptly asked the next group to occupy it.
When all of us got back, we had 4 strangers looking hungrily at our order of chicken something, Indian breads & something else!
I will not bother you with how the rest of dinner, drive to dessert place and drive back home went. But rest assured that we had few more nasty surprises.
The car that wont start
My kids have taken a strange fancy to keys & key holes. Once every few weeks they invent a hare-brained scheme to spoil a lock, key or something. Once when visiting family friends, my son has managed to bend my car keys by 900. Fortunately they had a hammer and we could twist it back.
But we are not always so lucky. Recently, in one of their invented games, they poked small sticks in to the key hole my car. Later when I tried to open the door, it was jammed. I ended up calling the service center folks and they sent a technician who gave me the look, “Sucker! I am sure you will become a regular customer”.
This is not a one off thing. I had same experience with my bike key hole, car trunk key hole and our gate locks. And did I tell you my kids derive a lot of pleasure locking the doors when we are in bath?!?
But its not all that bad
If there is one thing I am doing more after my kids are born, it is laughing & smiling. We (Jo and I) laugh at least a dozen times everyday watching the kids. They make us immensely happy. It is a joy to watch them grow, take good care of each other and learn things. Their innocent smiles and immense curiosity put us at ease almost immediately even when we are tired, stressed or worried. And that is worth more than a jammed key hole or spoiled dinner.
I wish you many more years of happiness, health & wealth Nakshatra & Nishanth. Now go and have the cake.
Just don’t spoil your new clothes 😛
PS: If you have kids, Learn Excel. You will have more free time to chase those little lunatics.
27 Responses to “Sum of Values Between 2 Dates [Excel Formulas]”
I would apply a filter and use function subtotal, with option 9. This way you can see multiple views based on the filter.
hey Chandoo, the solutions you proposed are very efficient, but if I wanted to be fancy I would do it this way .. the references are as your example workbook.
=SUM(INDIRECT("C"&(MATCH(F5,B5:B95)+4)):INDIRECT("C"&(MATCH(F6,B5:B95)+4)))
I like things simple:
=SUMIF(B5:B95,">="&F5,C5:C95)-SUMIF(B5:B95,">"&F6,C5:C95)
use something like: =SUM(OFFSET(B1,0,0,DATEDIF(A1,D1,"d")))
and have D1 be the date that I want to sum to.
In Excel 2003 (and earlier) I'd use an array formula to calculate either with nested if statements (as shown here) or with AND.
{=SUM(IF(B5:B95>F5,IF(B5:B95<F6,C5:C95,0),0))}
Note that I truly made this for BETWEEN the dates, not including the dates
I turned the data set into a table named Dailies.
I named the two limits StartDate and EndDate.
And used an array formula:
{=SUM((Dailies[Date]>=StartDate)*(Dailies[Date]<=EndDate)*Dailies[Sales])}
If I would still be using the old Excel I would do it as follows:
SUMIF($B$5:$B$95,"<="&H6,$C$5:$C$95)-SUMIF($B$5:$B$95,"<"&H5,$C$5:$C$95)
Works as simple as it is.
Regards
=sum(index(c:c,match(startdate,c:c,1)+1):index(c:c,match(enddate,c:c,1))
=sum(index(c:c,match(startdate,b:b,1)+1):index(c:c,match(enddate,b:b,1))
Great examples and thanks to Chandoo. You have simplified my work.
Hi! great tips I have found in your page, have you seen this
http://runakay.blogspot.com/2011/10/searching-in-multiple-excel-tabs.html
[...] I'm not sure I understand your question fully, but have a look at this: Sum of Values Between 2 Dates [Excel Formulas] | Chandoo.org - Learn Microsoft Excel Online [...]
Thank you! Thank you! Thank you!
=SUMIF(A2:A11;">="&B13;B2:B11)-SUMIF(A2:A11;"<"&A11;B2:B11)
awesome... thank yoo Chandoo!
which is most efficient and fast, if all are efficient ?
Thank you for this formula, I've just spent ages trying to find something to work on my data, I knew it would be possible! Don't care if others think there are easier/other ways to do it, you explained it so I understood it and could apply it to what I was doing so I'm happy!
The above said example is awesome for calculating values between dates,
can you pls let know how to calculate sale values if we have 10 sales boys for
ex: 1,rama
2,krishna
3,ashwin
4,naga
5,suresh
how much rama sale value between 1/jan/2015 to 10/jun/15
how much krishna sale value between 10/jan/2015 to 15/july/2015
i think you understood can you pls let me know the formula for how to calculate the sale between diffrent sale man sale value from master data file
Thanks,
Nagaraju
Hi
I have a list of people's names in column A, I have a list of dates in column B which records the dates they have been off sick, in column C I have either 1 if it is a full sick day or 0.5 if it is a half day.
What I would like to do is to add up the number of dates a specific person has been off within two dates.
For example, I want to look at my list of names and to find Joe Bloggs (column A), then add up all his sick days (column C). The start date will be in cell E1 and the end date will be in F1.
If this possible using SUMIFS?
List of names are in range A2:A100
List of dates in B2:B100
List of sick days (either 0.5 or 1 in C2:C100
The start date is in cell E2
The end date is in cell F2
Your help would be greatly appreciated.
Yes, with the help of SUMIFS you can have the solution.
Note: you need have an extra col. D2 where you will input Name of the person.
=SUMIFS(C2:C100,A2:A100,D2,C2:C100,">="&E2,C2:C100,"<"&F2)
Col. A Col. B Col. C Col.D Col. E Col. F
Name Date Sales
ABC 28-Jun-11 1 MNO 28-Jun-11 25-Sep-11
XYZ 29-Jun-11 0.5
MNO 30-Jun-11 1
PQR 1-Jul-11 1
Typo ERROR / Correction in formula:
Yes, with the help of SUMIFS you can have the solution.
Note: you need have an extra col. D2 where you will input Name of the person.
=SUMIFS(C2:C100,A2:A100,D2,B2:B100,">="&E2,B2:B100,"<"&F2)
Hi
I have a list of people's names in column A, I have a list of dates in column B which records the dates they have been off sick, in column C I have either 1 if it is a full sick day or 0.5 if it is a half day.
What I would like to do is to add up the number of dates a specific person has been off within two dates.
For example, I want to look at my list of names and to find Joe Bloggs (column A), then add up all his sick days (column C). The start date will be in cell E1 and the end date will be in F1.
If this possible using SUMIFS?
List of names are in range A2:A100
List of dates in B2:B100
List of sick days (either 0.5 or 1 in C2:C100
The start date is in cell E2
The end date is in cell F2
Your help would be greatly appreciated.
Viv
@Viv
Can you please post the question in the Chandoo.org Forums
http://forum.chandoo.org/
Please attach a file so that a specific answer can be delivered.
Thanks for this - it solved the problem that I was having. However can someone please explain to me why the "" needs to be around >= and <= as well as why we need to add & in order for the formula to work? Thanks in advance!
This formula works perfectly as well. Any ideas?: =SUM(INDEX(C5:C95,MATCH(H5,B5:B95,1)):INDEX(C5:C95,MATCH(H6,B5:B95,1)))
ikkeman had posted the same thing.
I am trying to sum total a range of cells between date ranges ie column n has $ amounts column d has the transaction dates ie 1/3/2015 or 25/3/2015 or 25/4/2015 column b has the text saying drp or distribution - reinv
In another cell I am trying to sum or total (in column n) with the value of a range of different dates (column d) that contain different text (column b) ie cell n48 is 50, n65 is 85, n165 is 36
with the dates ie cell d48 is 1/3/2015, d65 is 25/3/2015 and d165 is 25/4/2015
with different text that says drp or distribution - reinv ie cell b48 is drp, b65 is distribution - reinv, b165 is drp
If I wanted to sum the amounts between 1/3/2015 to 31/3/2015 with drp then the total would be 50. Also if I wanted to sum the amounts between 1/4/2015 to 30/4/2015 with drp the sum total would be 36 If I wanted to sum the amounts between 1/3/2015 to 31/3/2015 with drp and distribution - reinv the sum would be 115
What would the formula be for these different questions
hope you can help, it has been driving me nuts and cant work it out