Today, lets consider this question: Someone has a threshold value of $25000 in cell E1. We want to add up all the values in A2:A8 that are over the threshold. But...as a twist, how do we work with only those amounts in excess of the threshold? In Episode #1480 Bill shows how this can be accomplished.
...This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
...This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel from MrExcel, Episode 1480 – Sum > Threshold Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
And today's question is sent in via YouTube.
It’s funny how, you know, these questions come in and it can mean a lot of different things.
They said they have a series of numbers here, and they want to have all the numbers that are greater than the threshold.
So I said, okay, that's =SUMIF(B2:B8">"&E1).
We’ll take everything in B2 to B8 if it's...
Check This out, I concatenated a greater than sign with that value.
If it's greater than 25,000, we'll add that number up.
So that would be what we have over here in column A. If it's greater than 25,000, we add it up otherwise, 0 and we get the 132,000.
He said, “No, no, no, wait.
I only want the amount greater than the threshold.
So subtract the threshold from each one of those.
Okay, well that's going to be a little bit harder, but we can still do it because we're going to use the same formulas before, =SUMIF(B2:B8">"&E1) COUNTIF(B2:B8,">"&E1)"E1, and then count how many of these are greater than 25,000, and then multiply that by 25,000.
So in other words, for each one that's greater than, we're taking out.
I sent that formula back but then in the back of my head, I said, “Well, wait a second.
What happens here in cell F7?
Do you want the negative 1171 to be added in?” And he said, “Well of course, I do.” Oh, well.
Then that's a completely different problem altogether.
Now we're just going to, =SUMIF(B2:B8) E1"COUNT(B2:B17), sum up everything that's in B2 to B8 and then subtract E1, the threshold, times the number of numeric entries in B2 to...
I made it longer in case they added more.
Of course, you'd have to rewrite that formula.
So just a simple cell, minus the number of items, times the 25,000.
So in that case, it's everything in excess of 25,000, but for the numbers less than 25,000, we're getting negatives, and we get the 3814.
So three different ways to solve three different problems that all sounded very similar when the question was sent in.
Hey, I want to thank everyone for stopping by.
We'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel, Episode 1480 – Sum > Threshold Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
And today's question is sent in via YouTube.
It’s funny how, you know, these questions come in and it can mean a lot of different things.
They said they have a series of numbers here, and they want to have all the numbers that are greater than the threshold.
So I said, okay, that's =SUMIF(B2:B8">"&E1).
We’ll take everything in B2 to B8 if it's...
Check This out, I concatenated a greater than sign with that value.
If it's greater than 25,000, we'll add that number up.
So that would be what we have over here in column A. If it's greater than 25,000, we add it up otherwise, 0 and we get the 132,000.
He said, “No, no, no, wait.
I only want the amount greater than the threshold.
So subtract the threshold from each one of those.
Okay, well that's going to be a little bit harder, but we can still do it because we're going to use the same formulas before, =SUMIF(B2:B8">"&E1) COUNTIF(B2:B8,">"&E1)"E1, and then count how many of these are greater than 25,000, and then multiply that by 25,000.
So in other words, for each one that's greater than, we're taking out.
I sent that formula back but then in the back of my head, I said, “Well, wait a second.
What happens here in cell F7?
Do you want the negative 1171 to be added in?” And he said, “Well of course, I do.” Oh, well.
Then that's a completely different problem altogether.
Now we're just going to, =SUMIF(B2:B8) E1"COUNT(B2:B17), sum up everything that's in B2 to B8 and then subtract E1, the threshold, times the number of numeric entries in B2 to...
I made it longer in case they added more.
Of course, you'd have to rewrite that formula.
So just a simple cell, minus the number of items, times the 25,000.
So in that case, it's everything in excess of 25,000, but for the numbers less than 25,000, we're getting negatives, and we get the 3814.
So three different ways to solve three different problems that all sounded very similar when the question was sent in.
Hey, I want to thank everyone for stopping by.
We'll see you next time for another netcast from MrExcel.
