How to use COUNTIF to count survey answers for a subset of rows. Bill and Mike show many methods in Episode 1065.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Welcome back, it's another dueling Excel podcast, I'm Bill Jelen from MrExcel with Mike Grivin from Excel Is Fun.
Great question sent in today by a Youtuber.
It would be interesting to see how many different answers we come up with, here Carine BO3 has survey data, and he has dates, sector, and the answer.
He wants to count how many times each answer appeared; one, two, three, four, five.
Well, that's easy that's COUNT IF , but what field filter it to a certain sector or to a certain date so I'm going to do this with a Pivot Table, will choose one cell in the data go to Insert, choose Pivot Table and I'm not going to allow it to appear on its own work shame I put it out here on the right hand side we just put it out here in column F so we can see that the original data, now as I think about this I want answers it going across the top and dates going down the left hand side, also we want to be able to filter this by sector, so I'm going to put the sector up in the report filter section and then we have to put something in some values.
I want to choose something that is text because text is going to force it to count, so I'm going to take the sector and put that in some values and now if I scroll over you're going to see that for each answer we have the number of times that appeared on a single day, couple of things I want to do here first thing I want to do is I want to get rid of that grand total on the right hand side, so we are going into options and under totals and filters I'm going to turn off Grand Total For Rows; that will get rid of the grand total column all right that's good I also want to make sure that we fill in the blank cells with 0, so I should have done that when we're back in there under options layout and format for empty cells show 0, 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 that one do that by months and years, click OK, and we now have data by month.
Now, the other thing we want to be able to do is look for a specific sector by putting the data up here in the report filter section, I can open 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 it'd be easy now for me to come in and say all right I'm looking for 2008 I'm looking for just February and we're good to go we could see how many times each item appeared.
You know frankly I would want to see the percentage of total, so I'm going to do one more thing even though it wasn't in the original question I'm going to go into Field Settings and show values as a percentage of the row, click ok, all right now we get to see for each answer how many times it appears, so that's the Pivot Table solution allows you to go through and look up data for one specific sector, one specific month and get those answers see what Mike comes up with here.
Senator Mike!
Mike Grivin (03:21): Thanks MrExcel.
Hey I have my data set here and I had to make a little table because I'm going to use a formula even though if I wanted to do it the fast way I'd do it the way MrExcel did it, but sometimes you do want to use a formula.
Now I built this table I have a begin date and a column and an end date and in essence I did that so that I could build a formula and then change these, so I could have a 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 going to go ahead and zoom in here and start to build on the formula, now I'm going to use COUNT IFs with an S; that is a function that's only in Excel 2007 and I don't worry if you don't have Excel 2007, 2010 is coming out in six months and both of them have some great features, so you should get out there and get the new version.
So, here's COUNTIFs, equals COUNTIFs, oops I didn't use my S, now four criteria, four ranges, I'm going to start out with criteria range one, and criteria so that's all it needs for each one is the range and the criteria, click in the top cell for date, CTRL Shift Down Arrow to jump to the end , then F4 to lock it, comma and our criteria is going to be our begin date, so in Double Quotes I'm going to say greater than or equal to and Double Quote, and then Ampersand, and I'm clicking on the begin date now I got to 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 a lock there, so I'm going to hit the F4 key three times to lock the column reference.
Now watch this; I'm going to highlight this whole thing right here Copy, comma and then CTRL 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 going to 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'll 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; CTRL Shift Down, now F4, Comma and we don't need any Double Quotes here we just need to click right there.
Ah! but we need to lock it because when we copy the formula down it needs to be locked there but when you move it over needs to move to that too, so what do; I do F4, F4 row reference lock, Comma file and either the 4th range which is this one, Comma to get to our criteria and this one is going to be locked in all directions because we need that that's like the report or page filter for Pivot Table, so I hit F4 and close parentheses, CTRL, Enter to put that formula in the cell, oh!
I'm going to put the month here I had the quarter there now I'm going to 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 was like to check I'm going to come to the diagonally furthest one away and hit my F2 key Edit, sure enough the green one is there the two dates one and they're off that is just great cell reference formula work there, so there it is; I guess us what we wanted the main a point here is we can change this I'm going to 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 one now that's one option.
If you didn't have 2007 then you could use a some 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 negatives to convert the trues and falses to ones and zeros, and then 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 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 because 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 Database Function DCOUNT and all it requires is you put the field names exactly as they are over in that table and the criteria and watch this; total simple formula, your database is the whole database your field is the actual field name I put answer and then the criteria is this right here.
The only problem with DCOUNT and DBASS functions is they're hard to build huge tables of formula results, all right we'll see you next trick.
Bill Jelen (09:47): Hey, all right Mike thanks; that's a great technique boy you can tell it, I have the advantage by getting a go first I took the easy way out with a Pivot Table, but great formula there.
Hey next week I'm going to be on the road to my annual trip to Laguna Beach, so what I'm going to be doing is I'm going to be recording some podcasts 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 the 2010 watch next week or go through a lot of cool features for Excel 2010, come on me back with more Excel tips the week after that one.
Thank you for stopping by; we have a Mike and me, we'll see you next time for another dueling Excel podcast.
Great question sent in today by a Youtuber.
It would be interesting to see how many different answers we come up with, here Carine BO3 has survey data, and he has dates, sector, and the answer.
He wants to count how many times each answer appeared; one, two, three, four, five.
Well, that's easy that's COUNT IF , but what field filter it to a certain sector or to a certain date so I'm going to do this with a Pivot Table, will choose one cell in the data go to Insert, choose Pivot Table and I'm not going to allow it to appear on its own work shame I put it out here on the right hand side we just put it out here in column F so we can see that the original data, now as I think about this I want answers it going across the top and dates going down the left hand side, also we want to be able to filter this by sector, so I'm going to put the sector up in the report filter section and then we have to put something in some values.
I want to choose something that is text because text is going to force it to count, so I'm going to take the sector and put that in some values and now if I scroll over you're going to see that for each answer we have the number of times that appeared on a single day, couple of things I want to do here first thing I want to do is I want to get rid of that grand total on the right hand side, so we are going into options and under totals and filters I'm going to turn off Grand Total For Rows; that will get rid of the grand total column all right that's good I also want to make sure that we fill in the blank cells with 0, so I should have done that when we're back in there under options layout and format for empty cells show 0, 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 that one do that by months and years, click OK, and we now have data by month.
Now, the other thing we want to be able to do is look for a specific sector by putting the data up here in the report filter section, I can open 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 it'd be easy now for me to come in and say all right I'm looking for 2008 I'm looking for just February and we're good to go we could see how many times each item appeared.
You know frankly I would want to see the percentage of total, so I'm going to do one more thing even though it wasn't in the original question I'm going to go into Field Settings and show values as a percentage of the row, click ok, all right now we get to see for each answer how many times it appears, so that's the Pivot Table solution allows you to go through and look up data for one specific sector, one specific month and get those answers see what Mike comes up with here.
Senator Mike!
Mike Grivin (03:21): Thanks MrExcel.
Hey I have my data set here and I had to make a little table because I'm going to use a formula even though if I wanted to do it the fast way I'd do it the way MrExcel did it, but sometimes you do want to use a formula.
Now I built this table I have a begin date and a column and an end date and in essence I did that so that I could build a formula and then change these, so I could have a 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 going to go ahead and zoom in here and start to build on the formula, now I'm going to use COUNT IFs with an S; that is a function that's only in Excel 2007 and I don't worry if you don't have Excel 2007, 2010 is coming out in six months and both of them have some great features, so you should get out there and get the new version.
So, here's COUNTIFs, equals COUNTIFs, oops I didn't use my S, now four criteria, four ranges, I'm going to start out with criteria range one, and criteria so that's all it needs for each one is the range and the criteria, click in the top cell for date, CTRL Shift Down Arrow to jump to the end , then F4 to lock it, comma and our criteria is going to be our begin date, so in Double Quotes I'm going to say greater than or equal to and Double Quote, and then Ampersand, and I'm clicking on the begin date now I got to 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 a lock there, so I'm going to hit the F4 key three times to lock the column reference.
Now watch this; I'm going to highlight this whole thing right here Copy, comma and then CTRL 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 going to 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'll 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; CTRL Shift Down, now F4, Comma and we don't need any Double Quotes here we just need to click right there.
Ah! but we need to lock it because when we copy the formula down it needs to be locked there but when you move it over needs to move to that too, so what do; I do F4, F4 row reference lock, Comma file and either the 4th range which is this one, Comma to get to our criteria and this one is going to be locked in all directions because we need that that's like the report or page filter for Pivot Table, so I hit F4 and close parentheses, CTRL, Enter to put that formula in the cell, oh!
I'm going to put the month here I had the quarter there now I'm going to 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 was like to check I'm going to come to the diagonally furthest one away and hit my F2 key Edit, sure enough the green one is there the two dates one and they're off that is just great cell reference formula work there, so there it is; I guess us what we wanted the main a point here is we can change this I'm going to 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 one now that's one option.
If you didn't have 2007 then you could use a some 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 negatives to convert the trues and falses to ones and zeros, and then 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 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 because 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 Database Function DCOUNT and all it requires is you put the field names exactly as they are over in that table and the criteria and watch this; total simple formula, your database is the whole database your field is the actual field name I put answer and then the criteria is this right here.
The only problem with DCOUNT and DBASS functions is they're hard to build huge tables of formula results, all right we'll see you next trick.
Bill Jelen (09:47): Hey, all right Mike thanks; that's a great technique boy you can tell it, I have the advantage by getting a go first I took the easy way out with a Pivot Table, but great formula there.
Hey next week I'm going to be on the road to my annual trip to Laguna Beach, so what I'm going to be doing is I'm going to be recording some podcasts 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 the 2010 watch next week or go through a lot of cool features for Excel 2010, come on me back with more Excel tips the week after that one.
Thank you for stopping by; we have a Mike and me, we'll see you next time for another dueling Excel podcast.
