
more
Jeff Lenning, Author of Excel University
Former "Big 6" auditor. Accounting and finance roles at global companies. Published Excel expert. California CPA Education Foundation Award for Instructor Excellence.
This instructor has 11 courses »To Access This Course:
Hello and welcome Excel fans everywhere to our heavyweight event!
In one corner, with an impressive 20-year career, introducing the reigning champion of lookup functions, VLOOKUP. In the other corner, quickly gaining popularity with users, a recently introduced powerhouse, SUMIFS. Both functions have their unique ins and outs and situations in which you would prefer one over the other.
In this course, we’ll witness an epic battle between these two Excel heavyweights and watch these rivals go head-to-head using real-world examples and a full explanation of each. Who will win? Join us and find out…
Learning Objectives
- Explore the arguments of the VLOOKUP function
- Identify the SUMIFS function arguments
- Recognize the differences between these two functions
Last updated/reviewed: November 17, 2020
124 Reviews (375 ratings)Reviews
What I really liked about this course was the fact Jeff explained the mechanics of both formulas as well as the reasons behind needing the formulas. Also, the comparison of formulas helped with my understanding of their strengths and limitations. What surprised me the most was that these formulas can be used together, becoming a really powerful tool. I do however wish I was able to practice these using this formulas during the lecture myself with a practice spreadsheet with dummy data. That is the only thing I did not like about this course. Other than that, great job Jeff!
The course video itself was fine A++. My critique is the quiz.
The test questions were poorly worded. For example, when it asks about "performing a match", I wasn't sure at what point of the logic. VLOOKUP looks for an answer in the leftmost column, then spits out the number to the right. I interpreted this question as "after it has performed its match where does it go from there"? I was annoyed that I knew the answer, but I got it wrong because of the wording of the question.
This course was helpful in understanding when to use each function and how they differ from one another and how they can be used together. The step by step instruction was informative but I wish instead of repeating the instruction we were given a problem to solve by using the functions. Jeff made the course fun with his 'battle' reference. Anyone in Finance or who creates large worksheets with lots of data could benefit from this course and the use of these two functions.
What I did not get from this course was comparing to different worksheets of data elements and trying to look for matches (comparing 2 different reports to validate that data agrees). Also, when the exam is finished and the passing grade has been received, it would be nice to see the questions again so we can see which ones may have been incorrect and going back and reviewing the data again.
This course is geared towards beginner level excel users and provides a very details explanation of these two functions for users that are not familiar with them. Examples were a little long but were effective in showing how to use these functions, their differences, and limitations. Effective course, but material could have been covered in a shorter time period.
Great Course! I've been using Vlookup for years and used Sumif occassionally. SUMIFS will revolutionize my future work in Excel (AND SAVE STEPS!) - Thanks so much. Also, nesting sumifs in Vlookup is another revolution for me. I expect this to really help me in the future. I highly recommend this course if you summarize data or use lookup features in Excel.
Jeff is a boss! Absolutely my favorite presenter in this platform. As far as the course > Awesome! I love his courses, because they usually revolve around Excel which most accountants use at least once everyday! This is a must watch course for anyone like me who has used both functions, but not sure WHEN to use one over the other OR both!
This course gave me a better grasp of how to use the two functions VLOOKUP and SUMIFS and makes me feel more comfortable using them in my workpapers. The pace was not too tedious or rushed. It was not overcomplicated but demonstrated the fundamentals of the functions and relevant applications.
I am surprised that SUMIFs helped more than VLOOKUP in some of the rounds, I don't really use SUMIFS but now im happy that i know what to use in certain data findings. Also, I think Illumeo should attach a excel practice so the student can follow the instructor during some of the exercises.
Excellent course. Jeff Lenning was very entertaining while informative. I've always used false on vlookup and didn't really understand when true should be used. I now understand the difference. I also know now that Sumifs is a powerful tool.
I think this class was really helpful in explaining both functions. It was very clear when sumifs should be used vs. when vlookup should be used. I thought the practical applications were helpful in understanding the differences between the functions.
Thank you - great comparison of common formulas. Determining which formula is most effective for different sets of data is important to keep the size of spreadsheets down, manage data sets efficiently, and make the formulas easy to review.
I found this course especially informative. Sumifs can do a lot and combined they can do even more. I liked the instructor. He was very easy to follow and I liked that he goes over the formulas 2x to really understand it.
The section where the instructor compares Vlookup with Sumifs is extremely helpful. It illuminates under what circumstance to utilize each function and how they can be paired to yield the results users are looking for.
Very clever way to show comparison of the 2 and gave great real-world examples of when you would use each. Going over examples a few times was a bit repetitive, but for beginners would be necessary. Overall Great
I truly enjoyed this course. Learned everything I needed to know about these two Excel functions and had fun doing it! Jeff has a great sense of humor and keeps his audience engaged throughout!
Really great course. Succinct and instructive. I do wish the excel files used were available for practice after the course to really learn the material taught. Otherwise, I was very impressed.
Awesome course for learning the differences and similarities between these two formulas. Had some familiarity with VLOOKUP but learned a little more and definitely learned a lot about SUMIFS.
This course helped me understand SUMIFS better by learning the appropriate steps and column order. Knowing that I should first select the $$$ column and lock made following the logic easier.
This course really helped me to get a handle on the differences in the two functions. I hadn't realized how versatile SUMIFS was! As always, Jeff Lenning is great at breaking Excel down.
Instructor was very upbeat and thorough. Utilized all explanations of how to get the returned value looking for. I WILL be using SUMIFs a whole lot more now that I have taken this class.
By repeating all the exercises and formulas, it is now easier to remember the formulas. Practical summary of how to remember the formula versus the "code" is helpful. Clear and concise.
The explanations of the functions was very easy to understand. His presentation was great. I feel I have a solid understanding of how to apply the information in the presentation.
I learned some valuable information, however, it makes me cranky if the materials (i.e. spreadsheets) used for instruction are not provided as a download so that I can follow along.
So much more entertaining than most of the Excel courses I have taken. Jeff Lenning made the functions easy to remember by relating them to the spreadsheet data. Great course!
I would have liked to have had the worksheets to practice along with the instructor. Failing that it would have been nice to have the slides of the presentation to reference.
This course did a good job presenting the differences, strengths and weaknesses of both Vlookup and Sumifs. The approach was very pragmatic and applicable in the real world.
I think the course gave me a better understanding of both functions. I was very pleased with the examples and the way the instructor did the problems more than once.
Jeff makes a technical discussion a conversation which makes the details easier to understand. I would highly recommend this to anyone who uses the VLOOKUP function.
I loved this course. I didn't realize how to use the SUMIFS function and I will definitely use it now. I need to practice right away before I forget how to use it!
Awesome course, I highly recommend it for people who want to know more about both these functions. I wish the examples would have been a little more complicated.
The instructor explains the use of both the functions step by step and compares its benefits and shortfalls. This course is useful for advanced Excel users too.
Great explanation of each function. Love the use of multiple different examples. I finally feel like I understand when and how to use these two functions.
This was an awesome and valuable course. Learning the SUMIFS function will change my work efforts forever. Very thankful that I chose to take this course.
Typically I've resorted to using VLOOKUP however, after completing this course, can see the value in substituting SUMIFS or using a combination of the two.
A helpful and informative course on V-look up and SUMIFS functions. A good precursor to have an in depth knowledge and mastering functional excell skills.
First time using SUMIFS, liked the comparison between both functions. I can now use either to make my workbooks present information better and save time!
I wish the example spreadsheets had been included as course materials so that we could try the functions and check our results. Otherwise, great course.
I use vlookup and sumifs all the time, but had never thought about the differences. Thanks for illuminating them. I'll probably use sumifs more now!
Well taught explanation of these functions. But the examples and supporting materials are missing - adding them would be make this a 5 star course.
This course very informative. Jeff made the class fun & east to listen to. He explained the processes in a way that was easy to understand.
Presenter was very easy to listen to, kept the material moving at a good pace to keep interest, and used very relevant real world examples.
Nested vlookup and sumifs potential. Will use often. New to Illumeo and thought course was superior other courses with other CPE vendors.
Great course, I did not know how easy the sumifs function is to use and is something I know will make my excel workbooks more efficient.
i use both a lot every day but still found this to be helpful! i actually never knew what the fourth argument in vlookup was all about.
liked the presentation, but there should be a link to the excel spreadsheets so that we can follow along and practice at the same time.
Great approach to talk through a couple of times and show how it works practically - also like the adding in of using both together
Subject matter was clearly explained. Would have liked access to the examples so that I could try them before getting the answers.
Jeff kept the subject matter interesting and informative. I appreciated the review and consistent terminology used throughout.
The instructor was very effective in teaching these 2 functions and I believe this will be very helpful for me in the future.
The lecturer presents the information in an understandable and rational format. Very good examples with concepts reinforced.
I have always been worried about the VLOOKUP function and now I feel very comfortable with it. Thank you for a great class!
Good refresher on use of VLOOKUP and the "text" version of what's happening. I'm new to SUMIFS, so that was a good intro.
I learned SUMIFS function is more useful than VLOOKUPS in most cases which I used to use only VLOOKUPS. it's so amazing.
The lesson was put together very well. The examples were on point and specifically when doing side by side comparison.
Great examples ans comparisons. I could relate and think of ways to speed up everyday tasks using both in various ways.
Fun and informative way to present the practical uses for two related functions. Glad he added the way to use together.
Great presentation. The plain language for remembering functions is just what I needed to get them to stick. Thanks!
Knowing the differences between SUMIFS and VLOOKUP will be very helpful. Each have their strengths and limitations.
The presenter was engaging and enthusiastic.
This was presented very well. I use these functions often and still was able to learn some useful information.
Provides a very good explanation of the difference between the two functions and a unique way to combine them.
Course was presented in an effective manner with the right amount of repetition, pace and context. Well done.
This course gives great examples on how to apply the Sumif and vlookup functions and when to use each one.
very interesting to see them compared side by side. Makes it alot easier to use correct formula now
This was a very good course for excel user who are not familiar with the sumifs or VLookup functions.
This course was so very helpful. the way the instructor explained what the functions do was amazing.
good course, I liked Jeff's energy in presenting the material and it made the study more enjoyable.
A informative course and I learnt a few things that will surely help me with my Excel spreadsheet.
This was very good, hadn't heard of sumifs before. Helped me to understand vlookups better also
Excellent instructor. Entertaining and very informative. Increase in understanding of sumifs.
This course extended my understanding of both functions. Jeff made it fun and interesting.
Love that Jeff is so excited about Excel formulas! He definitely makes this lesson fun.
This was an awesome course to learn about the difference between sumifs and vlookups.
Good overview. I had not use SumIFS in many files but I will start to use them more.
This was the first course I tried and it was very helpful and clear. I like Illumeo!
The class was very informative and I would highly suggest my peers take this class.
Really appreciated the instructor's sense of humor - made a technical subject fun!
Excellent instructor. I especially liked the trick Jeff shared in Application 3.
The instructor kept my attention and did a great job of explaining the funcitons
Helpful course, always useful getting a refresher on these 2 functions. Thanks!
Great class and informative. Learned new things about both Vlookup and Sumifs.
sumif is a very complex formula but not as vlookup are. loved this self study!
Superior instruction! Great teaching style and easy to follow and understand.
This was the best explanation of VLOOKUP and SUMIFS that I have ever heard.
Great information that I will definitely use!!! Fantastic presenter too!
Love how excited instructor was! Made it easy to stay completely engaged.
I really liked the instructors enthusiasm and knowledge of the topic.
Great course. These are two of the better formulas to use within excel
This course gave very thorough explanations of the 2 Excel functions.
Gave great ways to understand the components of vlookup and sumifs
Very good review of both SumIfs and VLookup. I enjoyed the class.
The instructor made it interesting and was engaged throughout.
The course provided the required information quite effectively
good enough for me to get my cpes for my cia and cpa licenses
The Instructor is excellent and carries the course with ease.
I really liked the flow and the material of the presentation.
this class was great and I definitely learnt a lot of tips!
GREAT OVERVIEW - REFINES WHE TO USE WHAT FOR SPECIFIC NEEDS
clarity of concept explained followed by practice examples
Well explained and appreciated completing examples twice
Jeff's enthusiasm kept me engaged for the whole course.
It was very good, he is very much a likable character
I love your enthusiasm about Excel! Very informative.
Very good presentation on the argument to use SumIfs.
Jeff had go examples to follow and had good energy.
Pretty basic course. explained both functions well.
Really interesting deep dive on a cool Excel topic.
Love this course.
Entertaining and it hits home.
Went over good material and all types of examples.
A workbook to follow along with would be helpful.
The teacher had great energy and enthusiasm!
Excellent and impressive presenter!
Another useful course by Jeff.
good course on hard subject
New learning. Good session
Amazing course! Loved it.
Interesting presentation
Great course. Thanks!
Excellent comparison.
Well presented.
Great lesson!
Good job Jeff.
Nice
Prerequisites
Course Complexity: Intermediate
No Advanced Preparation or Prerequisites are needed for this course.
Education Provider Information
Company:
Illumeo, Inc., 75 East Santa Clara St., Suite 1215, San Jose, CA 95113
Contact:
For more information regarding this course, including complaint and cancellation policies, please contact our offices at (408) 400- 3993 or send an e-mail to .
Great course!
One question = Isn't the last final exam answer incorrect? I thought VLOOKUP would only find the first value, not the sum of all values, so I picked the second answer, but the course says that the first answer was correct.
Question:
If the lookup value is found on multiple rows in the lookup range, what does the function return?
Response:
Should have chosen
Both SUMIFS and VLOOKUP return the sum of all matching rows
Wrong
VLOOKUP returns the related value of the first matching row, and SUMIFS returns the sum of all matching rows
Neal...hmmm...thanks for the heads up. The correct answer is "VLOOKUP returns the related value of the first matching row, and SUMIFS returns the sum of all matching rows." I'll have to investigate the exam in this LMS. Thanks for letting me know!
Thanks
Jeff