Today's dueling Excel podcast question is how to count all cells with times greater than five minutes. Episode 1050 shows you how.
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:
Hey, I'm Bill Jelen from MrExcel.Com I've got a cool excel tip for you today.
Hey, this is Mike Gel Girvin and I am Excel Is Fun on Youtube and I have a different way to do that.
Hey, welcome back to the MrExcel netcast. It's a dueling Excel podcast.
I'm Bill Jelen from MrExcel and I'll be joined by Mike Gel Girvin from Excel Is Fun.
Question today came in, how we count times that are greater than five minutes?
So here's some sample times we want to count how many of those are greater than five minutes.
Before I jump into that, let's just talk about using the COUNTIF function a little bit.
Normally, you know so we have some numbers here in the way the COUNTIF typically is used.
= COUNTIF go look at this range And see how many of those are equal to the number 3.
3 being the criteria, so that shows us that there are 1 2 3 4 5 cells that are equal to three.
But it is possible to build somewhat complex criteria in here and we can put in quotes say everything that is > than or = 3 and that will count all 8 cells that are greater than 3.
So we're gonna talk today about building some sort of complex criteria.
A couple of ways to go, one is to say =COUNTIF and we specify this range of cells over here and then I'm going to build a complex criteria where I use the > sign and then an & and to get that time in there, I'm going to use the time function time.
Time 0 hours 5 minutes and 0 seconds.
and then another parenthesis to close the COUNTIF and we will see that there are in fact 4 cells that work.
Now if it's a hassle to you to use the time function, you could just come over here to the left and put in an actual time, so we have you know the 12:05 am, which is formatted show us five minutes and then use a formula here that says we're going to use the > sign and concatenate that with the value of D7 and that work and the beautiful thing here is that if we would change the criteria to be, may be 0:03:00.
It will do a different calculation.
0:04:00. Different calculation and so on...
The third method though, the one that seems just the easiest to me is to do =COUNTIF we'll look at our range over here and then we're going to build a criteria in quotes, which is > 0:05:00.
So we're actually formatting the time to how it looks back here and excel takes a look at that and understands what we're trying to do and sure enough comes up with the answer of 4.
So three different ways to solve this and we're going to turn it over to Mike and have Mike take a look at different ways he can come up with formulas to solve the same problem.
Thanks, MrExcel. Hey those were three amazing methods. Look at that one the time.
That's a great one.
This one right here very good because it's linked to the cell and this one wow with the time built right in.
Now, what lots of people like to hard code like this into the cell and if it's always five minutes that's certainly fine to do. We will do variation on that.
Let's think about how many hours there are in one day?
There's 24.
How many minutes?
60.
So total minutes in the day will be the product of those two and we have five minutes or our number of minutes that we're interested in is five minutes.
Now, what is time anyway?
Right if we type 8 AM, 8:00 AM.
If we control one to go look at the format Time right so it's time looks something like that, but if we get rid of, remember, we typed it in this way We typed it, that's how we typed it in but if we get rid of all the formatting with the general.
We'd see that It's a proportion of one 24-hour day, so we could actually build our formula 5 divided by 1440 so we could do =OUNTIF And get that range right there , and then in ">" and double quotes shift 7 for & and and then 5 /1440 close parentheses and that one will work.
Another variation if and for some times when you're dealing with time like 8 hours.
The fractional equivalent is really easy. It's just equals 1/3 right.
there's 18 out of three in 24 hours, so let's see if we can figure out if we're always dealing with five minutes.
Maybe we can see how many five minutes if we can reduce this fraction.
so How about just?
we're interested in five so I'm going to take the 1440/5 and certain if we get 288 so probably an even better formula if we were doing this all the time, and we like to do it this way.
It's just 1/288 now really in this case maybe you want to use one of these others but if you're dealing with time like our 8 hours or some and there's a simple fractional equivalent then go ahead and use that what but this one will work 1/288 all right?
We'll see you next trick. Hey, all right well How about that?
Next time someone should do something you're here to say that I'm gonna be there in five minutes me to say hey I'm gonna be there in one 280 eighth of a day. All right.
We have Mike and myself want to thank you for stopping by See you next time for another dueling Excel podcast.
Hey, this is Mike Gel Girvin and I am Excel Is Fun on Youtube and I have a different way to do that.
Hey, welcome back to the MrExcel netcast. It's a dueling Excel podcast.
I'm Bill Jelen from MrExcel and I'll be joined by Mike Gel Girvin from Excel Is Fun.
Question today came in, how we count times that are greater than five minutes?
So here's some sample times we want to count how many of those are greater than five minutes.
Before I jump into that, let's just talk about using the COUNTIF function a little bit.
Normally, you know so we have some numbers here in the way the COUNTIF typically is used.
= COUNTIF go look at this range And see how many of those are equal to the number 3.
3 being the criteria, so that shows us that there are 1 2 3 4 5 cells that are equal to three.
But it is possible to build somewhat complex criteria in here and we can put in quotes say everything that is > than or = 3 and that will count all 8 cells that are greater than 3.
So we're gonna talk today about building some sort of complex criteria.
A couple of ways to go, one is to say =COUNTIF and we specify this range of cells over here and then I'm going to build a complex criteria where I use the > sign and then an & and to get that time in there, I'm going to use the time function time.
Time 0 hours 5 minutes and 0 seconds.
and then another parenthesis to close the COUNTIF and we will see that there are in fact 4 cells that work.
Now if it's a hassle to you to use the time function, you could just come over here to the left and put in an actual time, so we have you know the 12:05 am, which is formatted show us five minutes and then use a formula here that says we're going to use the > sign and concatenate that with the value of D7 and that work and the beautiful thing here is that if we would change the criteria to be, may be 0:03:00.
It will do a different calculation.
0:04:00. Different calculation and so on...
The third method though, the one that seems just the easiest to me is to do =COUNTIF we'll look at our range over here and then we're going to build a criteria in quotes, which is > 0:05:00.
So we're actually formatting the time to how it looks back here and excel takes a look at that and understands what we're trying to do and sure enough comes up with the answer of 4.
So three different ways to solve this and we're going to turn it over to Mike and have Mike take a look at different ways he can come up with formulas to solve the same problem.
Thanks, MrExcel. Hey those were three amazing methods. Look at that one the time.
That's a great one.
This one right here very good because it's linked to the cell and this one wow with the time built right in.
Now, what lots of people like to hard code like this into the cell and if it's always five minutes that's certainly fine to do. We will do variation on that.
Let's think about how many hours there are in one day?
There's 24.
How many minutes?
60.
So total minutes in the day will be the product of those two and we have five minutes or our number of minutes that we're interested in is five minutes.
Now, what is time anyway?
Right if we type 8 AM, 8:00 AM.
If we control one to go look at the format Time right so it's time looks something like that, but if we get rid of, remember, we typed it in this way We typed it, that's how we typed it in but if we get rid of all the formatting with the general.
We'd see that It's a proportion of one 24-hour day, so we could actually build our formula 5 divided by 1440 so we could do =OUNTIF And get that range right there , and then in ">" and double quotes shift 7 for & and and then 5 /1440 close parentheses and that one will work.
Another variation if and for some times when you're dealing with time like 8 hours.
The fractional equivalent is really easy. It's just equals 1/3 right.
there's 18 out of three in 24 hours, so let's see if we can figure out if we're always dealing with five minutes.
Maybe we can see how many five minutes if we can reduce this fraction.
so How about just?
we're interested in five so I'm going to take the 1440/5 and certain if we get 288 so probably an even better formula if we were doing this all the time, and we like to do it this way.
It's just 1/288 now really in this case maybe you want to use one of these others but if you're dealing with time like our 8 hours or some and there's a simple fractional equivalent then go ahead and use that what but this one will work 1/288 all right?
We'll see you next trick. Hey, all right well How about that?
Next time someone should do something you're here to say that I'm gonna be there in five minutes me to say hey I'm gonna be there in one 280 eighth of a day. All right.
We have Mike and myself want to thank you for stopping by See you next time for another dueling Excel podcast.
