Скачать или смотреть Excel - SUMIF Function: Solve Complex Calculations with this Step-by-Step Tutorial - Episode 842

Microsoft Excel Tutorial: SUMIF Function: Solve Complex Calculations with this Step-by-Step Tutorial.

Welcome to another episode of the MrExcel netcast, I'm Bill Jelen. Today, we're going to tackle a common issue that many Excel users face - how to use the SUMIF function on a range of cells. But before we dive into that, I want to let you know about some upcoming seminars and events that I'll be attending. I'll be in Montgomery, Birmingham, Orange Beach, Atlanta, Akron, Springfield, and Roanoke in the next few weeks, so if you're in any of those areas, I'd love to meet up and talk about all things Excel. Just drop me a note and we'll make it happen.

Now, onto today's topic - using the SUMIF function on a range of cells. This question comes from Mark, who was struggling with using SUMIF on multiple tax lines and months. He found that he had to use multiple SUMIFS formulas to get the desired result, which can be time-consuming and tedious. So, I came up with a solution that I think will work for Mark and for anyone else facing a similar issue. I used a combination of the SUMIF and OFFSET functions to create an array formula that will sum up all the cells in a range based on a specific criteria. It may sound complicated, but I promise it's not as difficult as it seems. Just watch the video to see the formula in action and try it out for yourself.

To use this formula, you'll need to enter it as an array formula by pressing Ctrl+Shift+Enter. This will allow the formula to work on a range of cells instead of just one cell. And don't worry, I'll walk you through the steps in the video so you can see exactly how it works. I'm also interested to hear from you - do you have a different solution for this problem? If so, drop me a note at [email protected] and let me know. I'm always open to learning new techniques and sharing them with the Excel community.

Thanks for tuning in to this episode of the MrExcel netcast. I hope you found this tip helpful and that it will save you time and frustration in your future Excel projects. Don't forget to subscribe to our channel and hit the notification bell so you never miss an episode. And if you have any other Excel questions or topics you'd like me to cover, leave a comment below and I'll do my best to address them in future videos. Until next time, happy Excel-ing!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...


Marc sends in an interesting question about SUMIF. Each time the value is equal to January, he wants to grab a five-cell range! SUMIF doesn't seem up to the challenge, but an array formula with OFFSET does the trick. Episode 842 shows you how.

Table of Contents:
(00:00) Introduction to upcoming trips
(00:21) Schedule of upcoming trips
(00:40) Upcoming seminars and boot camps
(00:53) Invitation to attend seminars or meet up
(01:03) Today's question from Mark about SUMIF
(01:27) Solution to Mark's problem
(02:19) Explanation of formula and demonstration
(02:41) Request for alternative solutions
(02:51) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

This video answers these common search terms:
Akron
Atlanta
Birmingham
Montgomery, Alabama
Orange Beach
Roanoke
Springfield, Missouri
Springfield, Virginia
SUMIF
VLOOKUPs

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...