Authentic voices. Remarkable stories. AOL On Originals showcase the passions that make the world a more interesting place.
Go behind the scenes with some of the biggest digital celebrities to see what life is like when the blogging and tweeting stops.
The story of punk rock singer Laura Jane Grace of Against Me! who came out as a woman in 2012, and other members of the trans community whose experiences are woefully underrepresented and misunderstood in the media.
Documentary shorts conceived of and directed by famous actors. Jeff Garlin, Katie Holmes, Alia Shawkat, Judy Greer, and James Purefoy
Park Bench is a new kind of "talking show" straight from the mind of born and bred New Yorker and host, Steve Buscemi.
Digital influencer Justine Ezarik (iJustine) is back. After covering the world of wearable tech last season, iJustine is expanding her coverage this year by profiling the hottest tech trends across the country.
Enter the graceful but competitive world of ballet through the eyes of executive producer, Sarah Jessica Parker. This behind-the-scenes docudrama reveals what it takes to perform on the ultimate stage, the New York City Ballet. Catch NYCB on stage at Lincoln Center.
Nicole Richie brings her unfiltered sense of humor and unique perspective to life in a new series based on her irreverent twitter feed. The show follows the outspoken celebrity as she shares her perspective on style, parenting, relationships and her journey to adulthood.
Explore what it means to be human as we rush head first into the future through the eyes, creativity, and mind of Tiffany Shlain, acclaimed filmmaker and speaker, founder of The Webby Awards, mother, constant pusher of boundaries and one of Newsweek’s “women shaping the 21st Century.”
Gwyneth Paltrow and Tracy Anderson spend time with women who've overcome hardship, injury, and setbacks to triumph in the face of adversity.
Hank Azaria’s touching, humorous, and often enlightening journey from a man who is not even sure he wants to have kids, to a father going through the joys, trials and tribulations of being a dad.
ACTING DISRUPTIVE takes viewers inside the businesses and passion projects of Hollywood’s top celebrities.
Follow Scott Schuman, the Sartorialist, from the streets of NYC to the capitals of Europe on his quest to photograph and document the best in culture and fashion.
Learn how to use new functions in MS Excel 2007. In this lesson: SumIFs, AverageIFs, CountIFs, IFError, SumIF and more. I ...
cover all of these new functions in detail in my new Upgrading to Excel 2007 Seminar.
Tags:How to Use SUMIFS Function in Excel 07,599cd,excellearningzone.com,microsoft excel 2007 lesson,microsoft excel 2007 tutorial,new functions excel 2007,summation formulas excel 07,summation functions excel 2007
Grab video code:
Welcome to another free tip of the week brought to you by ExcelLearningZone.com. In this lesson, I’m going to show you the new SUMIFS function in Excel 2007.
Excel has had the SUMIF function for many versions now. That allows you to sum a range of values if a particular criteria is met. However SUMIF only supports one criteria. So in Excel 2007, Microsoft added the SUMIFS function that allows multiple criteria. And in this lesson I’ll show you how to use it. Here I have a real simple spreadsheet that has a sales rep, an office, in this case a state, New York or Pennsylvania, and total sales. Now I can add up the total sales down here easily with the sum function, equals the sum of, open parenthesis and I just simply highlight my range, in this case it’s C2 to C10 and press Enter and there are the total sales for all the sales reps and all the offices.
Now let’s see, I’ve got a complicated commission structure and I only want to calculate total sales if the sales are more than 50 for this period. So I’ll come over here and I’ll say over 50, that’s just my label. And now I’ll say equals SUMIF, open parenthesis, my range, same range, comma and now inside of quotes, I’m going to say greater than or equal to 50. Close my quotes, close the parenthesis and then press Enter. Now I get the total of all of these sales if that period is greater than or equal to 50. And you can see here it’s only 458. So the SUMIF did not add in the periods, such as this one, that’s 45. And if you do the math, yes that’s correct.
How about sales for just New York offices? Well, let’s put in here NY, just for New York. We’re going to use SUMIF again equals the SUMIF, open parenthesis. What’s my range? Well in this case, I want to look at this range of cells here, comma the criteria. Now I could put New York in quotes but I’ve got it right here so watch this, I’ll just click on that, F5, comma, now there’s a third optional parameter, it says sum range. What range do you want to sum up if the criteria is met? Well this is the sum range. And now I’ll press Enter and there we go. The sum for the New York offices is 393.
Now SUMIF is a great function but it only allows one criteria, that’s why in Excel 2007, they added the SUMIFS function that allows multiple criteria. So let’s say I want to find all sales for sales rep Todd from New York. All right so we need two criteria now. I’ll add those both here. I’ll put in Todd and New York. And I will use the SUMIFS function to find those matching sales. Equals SUMIFS, open parenthesis, what’s the sum range? The sum range is going to be my range of sales here, comma, criteria range one is going to be the rep, comma, now it says criteria one, what’s criteria one? Well that’s the sales rep’s name right there, E6, comma, criteria range two, well we’re looking for the office. So that’s right here, comma, criteria two, the office right there. And you can enter in pretty much as many of these criteria you want, it will take a bunch of them. When you’re done, close the parenthesis and press Enter. And now you’ll see the total sales for Todd from the New York office is 56. There’s only one right there. But I can easily change these by simply updating these cells. I’ll type in Pennsylvania, press Enter and look at that, it’s 45 and that is correct.
Let’s cheat and change some of our data. If I change this to Todd from Pennsylvania, look at that, my calculations over here updated. So as you can see, the new SUMIFS function adds extra functionality to SUMIF by allowing multiple criteria going across in the same row.
Now in addition to SUMIFS, there are also AVERAGEIFS and COUNTIFS functions that work pretty much the same as SUMIFS with a couple of little differences and I’ll show you how to use these in my full Excel courses. And of course, you can find out more on my website, ExcelLearnicZone.com, you can find lots more free tips just like this one and information on my full, hour-long Excel courses. YouTube viewers, go to ExcelLearningZone.com/YouTube and get a huge discount on any of my courses and you can watch my full Excel 101 course absolutely free of charge.