Home Archives for SUMIF and SUMIFS Functions
October 2, 2012 Leave a Comment

The 10 Most Watched Excel YouTube Videos for September 2012

My YouTube Channel – DannyRocksExcels – had 141,586 individual views during the month of September 2012:

Here is a list – with Hyperlinks – to the 10 Most Watched Excel Videos

Top 10 YouTube Excel Videos

  1. Compare Two Excel Lists to Spot the Differences– 5,304 views in September
    1. Go to this Blog Post on my website
  2. How to Merge Multiple Excel Workbooks to a Master Budget – 4,915 views in September
    1. Go to this Blog Post on my website
  3. How to Use Advanced Filters in Excel– 4,660 views in September
    1. Go to this Blog Post on my website
  4. How to Add a Check Box Control to an Excel Form– 4,301 views in September
    1. Go to this Blog Post on my website
  5. Use the Built-in Data Form in Excel to View and Filter Records– 3,948 views in September
    1. Go to this Blog Post on my website
  6. How to Create a Summary Report from an Excel Table-3,932 views in September
    1. Go to this Blog Post on my website
  7. How to Create an Interactive Excel Pivot Chart– 3,696 views in September
    1. Go to this Blog Post on my website
  8. Consolidating Data from Multiple Excel Worksheets by Position– 3,618 views in September
    1. Go to this Blog Post on my website
  9. How to Use the Solver Tool in Excel– 3,532 views in September
    1. Go to this Blog Post on my website
  10. How to Use an Excel Data Table for “What-if” Analysis– 3,513 views in September
    1. Go to this Blog Post on my website

Thanks to my 4,500 Subscriber on YouTube!

I extend my heartfelt thanks to my many loyal viewers and subscribers to my Excel video tutorials. Your feedback and requests for solutions have formed the foundation for my Excel Training Videos.

9 Essential Excel 2010 Skills

9 Essential Excel 2010 Skills

Learn More About my Extended Length Excel Video Training Resources

I have created a series of Extended Length Video Training Resources in the category of “Master Excel in Minutes.” I invite you to follow this link to learn more about my new series of videos.

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: COUNTIF and SUMIF Functions, Excel Data Tables and Lists, Excel Forms, Excel Formulas, Excel Functions, Excel Training, Lookup Functions in Excel, Most Viewed Excel Videos, Pivot Tables, SUMIF and SUMIFS Functions, Workbooks and Worksheets in Excel, YouTube Videos by Danny Rocks Tagged: , , , , ,
September 20, 2012 Leave a Comment

Instructional Guide “9 Essential Excel 2010 Skills” – Download for Free

Instructional Guide "9 Essential Excel 2010 Skills"

Instructional Guide “9 Essential Excel 2010 Skills”

I want you to discover the scope of the Excel Training that I offer on my new video tutorial, “9 Essential Excel 2010 Skills.” So, I am offering the PDF of the Step-by-Step Instructional Guide that accompanies the video tutorial.

Link to The Company Rocks Free Resources Page

Click on this link to begin the download process for the Instructional Guide.

Instructions for Downloading the Free Instructional Guide

  1. Add this product to your secure shopping cart at my website.
  2. During Checkout, you can choose to either Register or Shop as a Guest
  3. You will need to provide a valid email address in order to receive the actual link to download my Instructional Guide.
  4. Even though you will be downloading this product, I had to set it up as a physical product with “Free Shipping.”
  5. Shortly after you complete the checkout process you will receive an email that contains a hyperlink to the file that you will download.
  6. You will be downloading a “zipped” file. So, once the download is completed, be sure to “unzip” this file!
  7. I created my Instructional Guide using the free Adobe Acrobat Reader.

Read the Instructional Guide and then Purchase my Video!

I am convinced that once you see the scope of the training in Excel 2010 that I offer, you will want to purchase the video tutorial. I offer my video in tow versions:

List of the “9 Essential Excel 2010 Skills”

9 Essential Skills for Excel 2010

9 Essential Skills for Excel 2010 Video Tutorial

These are the 9 Essential Skills that I have identified for my video tutorial:

  1. Entering Data Efficiently in Excel 2010
  2. Selecting Cell Ranges Efficiently in Excel 2010
  3. Creating and Using Named Cell Ranges in Excel 2010
  4. Applying Styles and Formatting to Excel 2010 Worksheets
  5. Working with Structured Data Sets in Excel 2010
  6. Working with Excel 2010 Formulas and Functions
  7. Using Paste Special Options in Excel 2010
  8. Using Data Validation in Excel 2010
  9. Using Excel 2010 “What-if” Analysis Tools

Excel 2010 Practice Files Included

When you purchase my video tutorial, you receive the same Excel 2010 Worksheets that I used while filming each of the 25 video tutorials. Using the same files as you view my videos, you will be able to practice your new skills. And, of course, you also receive the same Instructional Guide that I am offering for free!

I welcome your feedback! Please send me your comments via email – danny@thecompanyrocks.com – or by adding a comment below.

Thank you!

 

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: COUNTIF and SUMIF Functions, Data Validation, Everyday Calculations in Excel, Excel Basics, Excel Data Tables and Lists, Excel Formulas, Excel Functions, Excel Training, Format Cells in Excel, Goal Seek in Excel, MS Excel Tips, MS Excel Training, Names for Cells and Ranges, New in Excel 2010, Online Store for The Company Rocks, Subtotals and Outlines in Excel, SUMIF and SUMIFS Functions, Video Training Courses, What-if Analysis Tagged: , , , , , , , , , , , , , , , , ,
December 4, 2011 Leave a Comment

How to SUM Values in One Field Based on Criteria from Multiple Fields in Excel

SUMIFS and DSUM Functions

SUMIFS and DSUM Functions

The new SUMIFS() Function was introduced in Excel 2007. With SUMIFS, you can sum the values in one field based up criteria that comes from multiple fields. This is a very valuable Function.

SUMIFS Function

The key to understanding SUMIFS, is that you “pair” a criteria range with the criteria for that range. As you watch my tutorial, the importance of this concept will become clear to you.

DSUM Function

If you are using – or need to create workbooks that are compatible with – older versions of Excel – e.g. Excel 2003, you can use the DSUM Function to achieve the same results. The DSUM belongs to the Database Functions set in Excel.

Use Named Cell Ranges in Formulas

I highly recommend that you learn how to create – and then use – named cell references in your Excel Formulas and Functions. In this tutorial, I show you how to do this. Once you have created a named cell reference, you can use the F3 Keyboard Shortcut to show a dialog box that lists all of the named Ranges that you can post into your formulas. This will save you time and help to ensure accuracy in your formulas – especially when you cop a formula to another location.

Bonus: Create Drop-down Menu with Data Validation

When using Multiple Criteria, I like to be able to select my criteria values from a drop-down list. In this lesson, I demonstrate how to do this using Data Validation in Excel.

Learn More Excel Tips

I invite you to visit my new, secure, online shopping website – http://shop.thecompanyrocks.com. Here, you can learn more about the tips on my best-selling DVD-ROM, “The 50 Best Tips for Excel 2007.”

 

Watch Video in High Definition

Here is the link to view this Excel Tutorial in High Definition on my YouTube Channel – DannyRocksExcels

YouTube Video

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: Data Validation, Excel Functions, Excel Training, Names for Cells and Ranges, New in Excel 2007, New in Excel 2010, SUMIF and SUMIFS Functions Tagged: , , , , , , , ,
June 8, 2011 Leave a Comment

Refer to an Excel Table Field When Writing Formulas

Refer to Excel Table in Formula

Refer to Excel Table in Formula

While I strongly recommend creating “Named Ranges” that you can refer to in an Excel Formula, there is one drawback. By default, “named ranges” refer to “Absolute” Cells – e.g. $A$1:$A$15

However, what happens when your data set expands? For example, when you add records beyond row 15 in this case.  Now, your formulas do not refer to the additional data in each field. You need to find a way to automatically expand the range of cells that your formula refers to.

Tables in Excel 2007 and Excel 2010

Fortunately, if you are using either Excel 2007 or Excel 2010, you can “format” your data set as an EXCEL TABLE and take advantage of many great options:

  • When you append records (rows) , the definition of your Table automatically expands.
  • You can “name” your table to reflect its contents. For example, in this video I name my Table – DRTable –  and I refer to it in my formulas.

Writing Formulas that Refer to Excel Table Field

In this video tutorial I use the SUMIF Function. Remember that I have “named”my table DRTable. Here is how I begin my formula. =SUMIF(DRTable[Customer]. Notice the use of brackets ([ ]) around the [Customer] Field in the formula. This is the gotcha step in this lesson.

Watch This Video in High Definition on YouTube

I created this video tutorial in High Definition Mode. Click on this link to view it on my YouTube Channel – DannyRocksExcels

Get my best-selling DVD-ROM, “The 50 Best Tips for Excel 2007” – Visit my Online Bookstore for more information.

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: Excel Data Tables and Lists, Excel Training, SUMIF and SUMIFS Functions, YouTube Videos by Danny Rocks Tagged: , , , , , ,
April 23, 2011 Leave a Comment

Create an Accounts Payable Summary Report in Excel

Accounts Payable Status Summary

Accounts Payable

Here is another video lesson that I created to answer a question from one of my viewers. In this case, the request is for help in creating an Accounts Payable Summary Report in Excel.

Formula to Create Label for Status

My viewer wants to know how to create one formula that will report the status of an Invoice as “Paid,” “Pay Now,” or “Delay.” To do this I “nested” one IF() formula inside another IF() formula. I used the ISBLANK() function as my first “logical test” in the formula. ISBLANK returns either TRUE or FALSE, so it is a good Function to become familiar with.

One of my “best practices” when learning how to “nest” IF Formulas, is to create each one separately – to test the results – and then copy and paste them into the combined formula.

Use SUMIF() Function to Total Status

Now, that your formula returns a status label for each invoice, you can use the SUMIF Function to search in the Named Range “Status” for the cells that match the criteria (Paid, Pay Now, Delay) and then Sum the Invoice Amounts for each category.

Watch This Video in High Definition on YouTube

Here is the link to my YouTube Channel, DannyRocksExcels, where you can watch this in High Definition.

Download this Excel Workbook

Follow this link to go to my Public SkyDrive to download this workbook to practice with.

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Create an Accounts Payable Report in Excel [ 8:15 ] Play Now | Play in Popup | Download (1680)
Link to this post!
Filed Under: Excel Financial Tools, Excel Functions, Excel Training, SUMIF and SUMIFS Functions Tagged: , , , , , , , ,
April 22, 2011 Leave a Comment

How to Use SUMIF in Excel to Total Year-to-Date Sales

One of my viewers asked for my help to use the SUMIF Function in Excel to total Year-to-Date (YTD) Sales. Generally, the SUMIF Function is very easy to set up and use. However, when you need to refer to a “date” in the criteria argument, there is a “Got’cha” step.

How to Write the Formula for Year-to-Date Sales

=SUMIF(Date, “<=”&TODAY(), Sales) Notice how the <= comparison operator is enclosed within ” ” (Quotation Marks) and I use the & (ampersand) to join the TODAY() Function. Be careful when you write this fomula. In my experience, when I try to write THIS formula in the “Functions Argument” Dialog Box, I almost always get an Error Message.

Create and Use Named Cell Ranges in SUMIF Function

As a best practice, I encourage you to create and use “Named Cell Ranges” in your formulas. This is especially helpful with the SUMIF, SUMIFS, COUNTIF, COUTIFS functions because you are usually looking inside extensive ranges of data.

Another Way to Total Year-to-Date Sales

For many years, I did not realize that there was a SUMIF Function. So, I used to create an extra column and write to formulas to compute the YTD Sales. If you do want to or need to see YTD sales in each cell, I show you how to do this by writing just one formula.

Watch Lesson in High Definition

Follow this link to view this video lesson in High Definition on my YouTube Channel – DannyRocksExcels

Download My Free Video Lessons on Pivot Tables

Click here to learn about my 24 minute video recordings “Introducing Pivot Tables.” I have two versions – One for Excel 2003 and one for Excel 2007. I am offering both of them as a “free of charge” downloadable product!

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
How to Use the SUMIF Function to Total Year to Date Sales [ 5:09 ] Play Now | Play in Popup | Download (1424)
Link to this post!
Filed Under: Date Functions and Formats in Excel, Excel Training, Podcast Tips & Timesavers, SUMIF and SUMIFS Functions Tagged: , , , , , ,
December 7, 2010 2 Comments

Use the SUMPRODUCT Function to Total Lists Using Multiple Criteria

In Excel 2007 and Excel 2010 you can use the new SUMIFS Function to total a list using multiple criteria. This is a great new feature!

But … what if you are using Excel 2003 or earlier? Or, if you collaborate with colleagues and clients who use older versions of Excel – how can you use multiple criteria to select the records that you want to Sum? You can use the SUMPRODUCT Function as I demonstrate in this video lesson.

The 50 Best Tips

The 50 Best Tips Series

Key Concepts

  • Create – and use – Named Cell References for Ranges / Arrays in your formulas
  • Use the Function Arguments Dialog Box – Use the keyboard shortcut Ctrl + A to bring this up
  • Use the F3 Keyboard Shortcut to bring up the Named Ranges List
  • In this lesson, use () to enclose each Array Argument
  • In this lesson, use * to substitute for the , to separate each Array in the formula (Edit this in the Formula Bar)

There are usually several ways to solve any problem in Excel. This is a good procedure to learn – regardless of the version of Excel that you use.

Click here to view the 1st part of this series on how to use the SUMPRODUCT Function in Excel.

You can view this Excel Video in Hig Definition, Full Screen Mode on my YouTube Channel – DannyRocksExcels by clicking here.

I cover the SUMIF and SUMIFS Functions in greater detail on my DVD, “The 50 Best Tips for Excel 2007.” Click here to start shopping using my secure shopping cart.

Learn how to “Master Excel in Minutes – Not Months!”

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Using the SUMPRODUCT Function to Total a List Using Multiple Criteria [ 7:47 ] Play Now | Play in Popup | Download (1121)
Link to this post!
Filed Under: Excel Functions, Excel Training, Podcast Tips & Timesavers, SUMIF and SUMIFS Functions Tagged: , , , , , , , , ,
October 25, 2008 Leave a Comment

Use the =SUMIF() Function to Tally YTD Sales

Many of my clients keep a “running total” of sales. They put “Dates” in Column “A” and enter each day’s sales in Column “B.” Next is Column “C” which contains the formula =SUM(B$:B250). That is fine – it works.

However, if you use the =SUMIF() function to total Year-to-Date (YTD) Sales you gain flexibility, save space and save time – because you do not have to scroll down several screens to see your YTD Sales total!

In this video, I show you how to use “Named Ranges” in a =SUMIF() Formula to keep track of your cumulative sales total.

Here are the steps to follow in this lesson:

  1. Traditionally, you use a formula like =SUM(B$3:B250) to give you a “running total” for YTD Sales.
  2. The=SUMIF() Function returns the SUM of vales that meet a “Single Criterion” (the IF portion) .
  3. The 1st Argument is – What is the Range that you want your criteria to evaluate? Consider “Naming” this Range – it makes it easier to write your formula and explain your formula.
  4. The 2nd Argument is – What is your criteria? Be sure to use this syntax – “Comparison Operator”
  5. In the video example we also need to include the “&” to join the cell “D2.” Cell “D2,” in this example, contains the Function =TODAY()
  6. The 3rd Argument is – What Range do you want to SUM to return the values that meet your Criteria?
  7. The =DATE() Function returns the serial number for a specific date. It requires 3 Arguments. =Date, Year, Month, Day)
  8. You will save time and gain productivity when you “Name Cells and Ranges” and then you them in your formulas.
  9. Use the F3 shortcut key to “Paste Names” into your formulas.

News! My DVD, “The 50 Best Tips for Excel 2007” is now available to purchase. I invite you to visit my online bookstore for more details.

Find the Excel Video Lesson that you want – Index to all Excel Topics

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: Excel Date and Time Functions, Excel Functions, Excel Training, SUMIF and SUMIFS Functions Tagged: , , , , ,