Bill: Hey, I’m Bill Jelen, Mr. Excel.com, I’ve got a cool Excel tip for you today.
Mike: Hey, I’m Mike Gel Girvin, Excel is Fun in Youtube, and I have a different way to do that.
Bill: Hey, alright, welcome back, it’s another Dueling Excel podcast, I’m Bill Jelen, Mr. Excel.com. I have Mike Girvin from Excel is Fun. Great question today, send in by Youtuber, be interesting how many different answers we come up with here CareenBO3 has survey data. He has dates, sector, and the answer, he wants to count how many times each answer appear, 1, 2, 3, 4, 5. Well that’s easy, that’s count if, but, he wants to be able to filter it to a certain sector or to a certain date. So, I’m gonna do this with a pivot table, which use one cell in the data, go to insert, choose pivot table, and I’m not gonna allow it to appear on its own worksheet, put it out here on the right hand side, just put it right here on our column F, we can see that the original data. And now, as I think about this, I want answers going across the top and dates going down to the left hand side. Also, I wanna be able to filter this by sector, so I’m gonna put the sector up in the report filter section. And then, we have to put something in sum values, I wanna choose something that is text, because, text is going to force the counting, I’m gonna take the sector and put that inside sum values, and now if I scroll over, you’re going to see that for each answer, we have the number of times it appeared on a single date. Couple of things I wanna do here, first thing I wanna do is I wanna get rid of that grand total on the right hand side, so we go into options, and under totals and filters, I’m gonna turn off grand total for rows. That will get rid of the grand total column, alright, that’s good. I also wanna make sure that we fill in the blank cells with zeros. So, I should have done that when we’re back in there, under options, lay out a format for empty cells, show zero, click OK, and now I have daily dates going down the left hand side. That’s not what I want, I want to group those up, so I choose the first one, choose group field and, say I wanna do that by months and years, click OK, and we now have data by month. Now, the other thing I we are able to do is look for a specific sector by putting the data up here in the report filter section, I can open this up and see one specific sector, sector one. Now, if in fact we had wanted to just filter to a specific month, I could, now that this is grouped, take years and date up and add that to the filter as well. Now, I would be easy enough for me to come in and say, alright, looking for 2008, I’m looking for just February and we’re good to go. We get to see how many times each item appeared. You know frankly, I would wanna see the percentage of total, so I’m gonna do one more thing, even though it wasn’t in the original question, I’m gonna go into field settings and show values as a percentage of the row, click OK. Alright, now we get to see for each answer how many times that appeared. So, that’s the pivot table solution. As long as you go through and look up data for one specific sector, one specific month, and get those answers. See what Mike comes up with here, send it over to Mike.
Mike: Thanks Mr. Excel. Hey, I have my data set here and I had to make a little table, coz I’m gonna use a formula, even though if I wanted to do it the fast way, I do it the way Mr. Excel did, but sometimes you do wanna use a formula. Now, I built this table, I have a begin date and a column and the end date, and since I did that, so that I could build a formula and then change these so I could have month or week or quarter. So, we’re having two criteria here for date, then we have the actual criteria for the question, and we have a fourth criteria up here, sector. So, one, two, three, four criteria have to be considered for our count here. Now, I’m gonna go ahead and zoom in here, and start to build the formula. Now, I’m gonna use count ifs, with an S, that is a function that’s only in Excel 2007, and, don’t worry, if you don’t have Excel 2007, 2010 is coming out in 6 months, and both of them have some great feature, so you should get out there and get the new version. So, here’s count ifs, equals count ifs, oops, I didn’t use my S, count ifs. Now, four criteria, four ranges. I’m gonna start off with criteria range one, and criteria. So that’s all it needs for each one, is the range and the criteria. Click on the top cell for date, control shift down, and alt to jump to the end and then F4 to lock it. Comma, and our criteria is gonna be our begin dates, so in double quote, so I’m gonna say greater than or equal to and double quote and then ampersand. And I’m clicking on the begin date, now, I’m gonna think about this, cell references. So, when I copy it down, I want this begin date to move, but when I copy it over to this column, I need it to lock there, so, I’m gonna hit the F4 key three times to lock the column reference. Now watch this, I’m gonna highlight this whole thing right here, copy, comma, and then control V, now that’s the same I just did, but watch this, I can double click that and click there and then hit F4 to lock it, column, but not the row, and I’m gonna change this to less than or equal to. Because we got to check that same date range but less than or equal to that, that will give us the difference between the two, any, anytime we get a true here and a true here, it will be in this particular time period. Now, the screen tip is very polite, we have our criteria two, so we put comma, and sure enough the next criteria range and criteria three come up. Now let’s go ahead and do this one, so I scroll over, get my answer, control shift down there on F4, comma, and, we don’t need any double quotes here, we just need to click right there. But we need to lock it, coz when we copy the formula down, needs to be lock there, but when we move it over, needs to move to that to, so what do I do? F4, F4, row reference lock, comma, finally I need the, the fourth range, which is this one. Comma, to get to our criteria, and this one is gonna be lock in all direction, coz we need that, that’s like the report or page filter for pivot table, so I hit F4, and close parenthesis. Control enter, to put that formula in the cell, ooh, I have the, I’m gonna put the month here, I had the quarter there. Now, I’m gonna copy this down, let go and then grab that fill handle again and copy it over, and just like that, you know, when I’m doing big formulas like this, I always like to check, I’m gonna come to the diagonally for this going away and hit my F2 key edit. Sure enough, the green one is there, the two dates one in there, oh, that is just great cell reference formula work there. So there it is, gets what we wanted, the main point here is we can change this, I’m gonna change this to three, I get my quarter results, I could easily change sector four, sector five and instantly the whole table updates. I’ll change this back to the one, now, that’s one option. If you didn’t have 2007, then you could use a sum products formula like this. Now the only difference is, we still have to have our range of values and our criteria, but we’re comparing it directly inside a parenthesis double negative to convert the trues and falses to ones and zeros. And there’s the four criteria and ranges. Still, another option, if we come over to this tab right here, if you have criteria January, February, March, how in the world do you compare text to this date range over here which are serial numbers? Well, then you could use the text function, now the text function is great, coz it will convert that whole range to whatever format, that’s a custom number format that converts all those serial dates to MMM, which gives us three letter text abbreviations for that month. Now, you cannot use this with count ifs, because this text function converts that whole range to an array, and so you have to use some product, some product can handle arrays. Still, another great option, if you really only want one particular set of criteria at a time instead of a whole table, all of the months, all of the number questions, you could use the data base function decount, and all it requires you is you put the field names exact, exactly as they are over on that table, and the criteria. Now, watch this, total, simple formula, your data base is the whole data base, your field is the actual field name, I put answer, and then the criteria is this right here. The only problem with decount and deep base functions is they’re hard to build huge tables of formula results. Alright, we’ll see you next trick.
Bill: Hey, alright Mike. Thanks, that’s a great technique. You can tell it, I have advantage by getting to go first, I took the easy way out with the pivot table, but great formula there. Hey, next week, I’m gonna be on the road, my annual trip to Laguna Beach, so, what I’m gonna be doing is I’m gonna be recording some podcast ahead where we take a look at the new features in Excel 2010. So if you haven’t upgraded yet to 2007 or you’re thinking about upgrading to 2010, watch next week, we’ll go through a lot of cool features for Excel 2010. And we’ll be back with more Excel tips the week after that. Well, thank you for stopping by, in behalf of Mike and myself, see you next time for another Dueling Excel podcast.
Transcription by:
Scribe4you Transcription Services