Bill: Welcome back, time for another Duel Excel podcast. I’m Bill Gillin from MrExcel. I’ll be joined by Mike Gervin of ExcelIsFun, this is Episode 120. Total by week and month. Well, a question posted today. In an effort to keep track of withdrawals, he logs how many withdrawals he makes each day, wants to keep a weekly total, and a monthly total, weeks always start on Monday, even if you went in the last month. Well I kind of imagine, and I have a spreadsheet, dates going through the left-hand side, and a pull up. Let’s, firstly, the weeks always start on Monday, so I can go to the calendar on the wall, or I can just change this to a temporarily long date. Well, we know this is Tuesday, and I’m going to review, Ctrl + Z, and we want to start on 12/31/2012. So it will be called the week of the week, and building weeks. I’ll be using the previous cell +7, let’s copy that, okay, go, week.
Well, to see how many withdrawals we’ve made during the week from 12/31 to 06/01, I’m going to use = SUMIFS, the new version of SUMIF, they came in Excel 2007. So our sum is going to be this range over there, F4. First criteria range, we’ll go to all dates, hit F4, and see if it’s> =, so watch this, I’m going to build this up right away, “> =” & the date to left me. Well this will build up a little> = 1/1/2013. This is a bit frustrating, the next criteria range is exactly the same as before, but I still have to specify that, F4, comma, and this time the criteria will be “<” and the date is in the next row.
Let’s see, the total is 420, and the total is 420. Well it looks cool, copy that well. Well that gives us our week. Now how about monthly? So we’ll be going on 1/1/2013, this time I’m going to fill in the months by right-clicking and dragging the fill handle, and we’ll say “Fill Months”. Okay, the formula here, hey, you know what, it’s the same formula here, we’re just copying it, copying it. Well rock and roll, this is a month. If you don’t want dates here then let’s go, and we’ll do a custom number format, Ctrl + 1, and in Custom just type “mmmm”, here we go. Okay Mike, let’s see what you’ve got! Mike: Thank you MrExcel! Oh man, SUMIFS this is a great job, and I’ll be using this one too, maybe I’m doing something a little different with the dates, but here’s what I want to know.
SUMIFS, right, MrExcel and I say the new SUMIFS, well, it’s been around since 2007, right. And I know a lot of people have 2003 and earlier. But SUMIFS does many amazing things, it’s worth getting 2007 or later, one of the versions, just forget about this functionality. So at some point we’ll have to stop saying new, true, and just say the SUMIFS function. Well, I’ll create the upper and lower bound between two dates.
I’ll be creating formulas for this, based on what date I am writing here. So I’ll start first in the month, I’ll be using the EOMONTH function. Now, in previous releases, 2003 and earlier, you can get this functionality if you add data analysis toolkit under the tools menu. I’ll say “Hey, this is the date”, comma, remember now, I’m trying to figure out March 1st, EOMONTH calculates the end of the month.
Watch this, I’ll say “months”, go -1, this will give me the end of the previous month. Now I can simply +1. Now that’s the beginning, I need the end, so now I’m using the end of the month for what it is. I say “There is a serial date”, 0 says “Give me the end of this month!” And now I’m going to use the same beautiful formula that MrExcel used. Notice now, I have the first and the end of the month, whereas MrExcel only has the first month. So, my class has to be, the comparative operator should be> = for the start date, because I want to include any of those dates, and <= for the end date, because I have to include it too. Well what are we going to do now for weeks? I’m going to do the same, beginning of the week, end of the week, both, low and high, should be included.
Okay, so this is Friday, well, if I were to take that date and subtract a number of days, how many days would I have to go backwards to get to Monday? Okay 4, right, Friday, Thursday, Wednesday, Tuesday, Monday, that’s 4 days. So we can use the WEEKDAY function. Now let’s take a look at the WEEKDAY post. So cool, I give it a serial number, then look at this, the return type, look at the nice dropdown list. Now remember, our target was -4 of Friday, and look at this, if we used 3, Monday is 0, Tuesday is 1, 2, 3, 4, it will be Friday, so 6 is Sunday. So beautiful, exactly what we want here. I just have some dates here, right, just to show you, here it is. When we get to Friday, we’re subtracting the actual serial date, we’re subtracting from it, the WEEKDAY 4 score, exactly what we want. ready? Equal to, 3, look at that, and this will always give us a Monday, so whatever we write here, this will change, it will change.
Now that’s, I’m going to +6. Now I want to copy this, but the problem is the way I’m trying to do it, right ??? I have any date here, the month, and all the weeks, sometimes we might get 5, 4, or 6, right ??? So I need to do something here, because I can go like this, +7 and then copy this, for this particular month I’m going to get quite a lot. For September, if I do it here (?) Then it will work, right, because if I were to go back to the previous Monday, I’d be spending 6 weeks, Ctrl + Z. So I’m going to change this here, and add IF. I would say “IF MONTH,” and MONTH would give me a number from 1-12, “IF THIS MONTH +7,” correct. So I want to know if this date is +7, that month, equals this month, F4 is locked. If it is true then I am running it there, otherwise let’s show an empty string, “” so that will stop. If I change this to 9 it will work fine, Ctrl + Z.
I’ll have to do something similar here, = if this equals, and you’ve already proven that there will be an empty text string, if it’s an empty text string, please show another empty text string, otherwise run that. Lo and behold, I actually have a SUMIF here, it’s the exact same exact formula as MrExcel did here, and copied here because we have lower and higher limits in this formula here, they’re both listed. If you are using earlier versions, and you don’t have SUMIF, you can use SUMPRODUCT, the very cool SUMPRODUCT. Well, throw it back to MrExcel! Bill: Hey, okay Mike! Boy, EOMONTH, love it, WEEKDAY job, beautiful spot. Hey, as I was thinking about it, you know, if it’s going to be entering new data while it’s going, these formulas I wrote have to be modified. It might make sense to Ctrl + T and make this in a table here. So let’s check it, this formula that’s currently going through row 90, let’s scroll down, and we’ll add more dates, take the fill handle, and drag.
I’ll be doing 1,000 every day just to make sure it really goes up, and it’s back, and you see our formulas now automatically extend. I think this is a crucial piece if he’s going to enter more cloud data every day. Well hello hello I would like to thank everyone for stopping by, we’ll see you next week on another duel excel podcast from MrExcel and ExcelIsFun! .