Microsoft Excel

2.1 Opening Microsoft Excel 2.2 Microsoft Excel Startup Screen 2.4 Introduction to the Excel Interface 2.5 Customizing the Excel Quick Access Toolbar 2.6 More on the Excel Interface 2.7 Understanding the Structure of an Excel Workbook 2.8 Saving an Excel Document 2.9 Opening an Existing Excel Document 2.10 Common Excel Shortcut Keys 3.1 Entering Text to Create Spreadsheet Titles 3.2 Working with Numeric Data in Excel 3.3 Entering Date Values in Excel 3.4 Working with Cell References 3.5 Creating Basic Formulas in Excel 3.6 Relative Versus Absolute Cell References in Formulas 3.7 Understanding the Order of Operation 4.1 The structure of an Excel Function 4.2 Working with the SUM() Function – Excel 4.3 Working with the MIN() and MAX() Functions 4.4 Working with the AVERAGE() Function 4.5 Working with the COUNT() Function 4.6 Adjacent Cells Error in Excel Calculations 4.7 Using the AutoSum Command 4.8 Excel’s AutoSum Shortcut Key 4.9 Using the AutoFill Command to Copy Formulas 5.1 Moving and Copying Data in an Excel Worksheet 5.2. Inserting and Deleting Rows and Columns 5.3. Changing the Width and Height of Cells 5.4 Hiding and Unhiding Excel Rows and Columns 5.5 Renaming an Excel Worksheet 5.6. Deleting an Excel Worksheet 5.7 Moving and Copying an Excel Worksheet 6.1 Working with Font Formatting Commands 6.2. Changing the Background Color of a Cell 6.3. Adding Borders to Cells 6.4. Formatting Data as Currency Values 6.5. Formatting Percentages 6.6. Using Excel’s Format Painter 6.7. Creating Styles to Format Data 6.8. Merging and Centering Cells 6.9. Using Conditional Formatting 7.1 Inserting Images and Shapes into an Excel Worksheet 7.2 Inserting Shapes In Excel 7.3 Formatting Excel Shapes 7.4. Working with Excel SmartArt 8.1. Creating an Excel Column Chart 8.2. Working with the Excel Chart Ribbon 8.3. Adding and Modifying Data on an Excel Chart 8.4. Formatting an Excel Chart 8.5. Moving a Chart to another Worksheet 8.6. Working with Excel Pie Charts 9.1. Viewing your Document in Print Preview 9.2. Changing the Margins, Scaling and Orientation 9.3 Adding Header and Footer Content 9.4 Printing a Specific Range of Cells 10.1. Intro to Excel Templates 10.3. Opening an Existing Template 10.4. Creating a Custom Template 13.1 Understanding Excel List Structure 13.2 Sorting a List Using Single Level Sort 13.3 Sorting a List Using Multi-Level Sorts 13.4 Using Custom Sorts in an Excel List 13.5 Filter an Excel List Using the AutoFilter Tool 13.6 Creating Subtotals in a List 13.7 Format a List as a Table 13.8 Using Conditional Formatting to Find Duplicates 13.9 Removing Duplicates in Excel 14.1 Excel DSUM Function Single Criteria 14.2 Excel DSUM Function with OR Criteria 14.3 Excel DSUM Function with AND Criteria 14.4 Excel Function: DAVERAGE() 14.5 Excel Function: DCOUNT() 14.6 Excel Function: SUBTOTAL() 15.1 Creating an Excel Data Validation List 15.2 Excel Decimal Data Validation 15.3 Adding a Custom Excel Data Validation Error 15.4 Dynamic Formulas by Using Excel Data Validation Techniques 16.1 Importing Data from Text Files into Excel 16.2 Excel 2019

14.1 Excel DSUM Function Single Criteria

By utilizing the DSUM function we can add criteria to the sum we’re going to say hey excel. I want to sum up the total sales but I only want to do it where the category is equal to rents, or where the categories equal to telemarketing or whatever it happens to be whatever our criteria is for our SUM, let’s take a look at the first one here. There’s actually a few different types of database functions DSUM just happens to be one of them. So let’s take a look at working with DSUM now in order for the DSUM to work, our first step is we have to set up the criteria section of the function we essentially need to identify which column we want to search for a value.

For example, we’re say Hey Excel I want you to go to the category column and I want you to find rent and then give me the sum of that record.

I’m going to put in a couple little pieces of information that’s going to help assist with this function.

The first thing, I’m going to put in here and I’m going to start this inside of it’s actually start the first thing I’m going to put in here and I’m going to start this inside of it’s actually start, this is important because that text straight there matches this text right here has to be spelled the same way, no extra spaces in there right if I go into that cell go to the formula bar it’s just straight up category. There’s nothing else in there and it’s spelled just like it is here inside of the list. That’s very important because that’s how Excel is going to identify the column that we want to search for our criteria with that now right below that’s in the next adjacent cell down.

I’m going to put in the criteria that I wanted to search for, in this case I’m just type in rent.

We’ve got the setup to use one of our database functions.

In this case the DSUM function, we’ve got our lists just there on the left.

I want you to sum up this column where this is true category equals rent.

The DSUM has three arguments three pieces of information that it needs from us in order for it to give us the total sales based on the criteria.

Excel DSUM Function Single Criteria 1

Now to make this a little bit easier to read once I got that equals DSUM open parentheses, I’m going to go quick on the Fx button, this should open up my argument window or if you’re on a Mac you know open up the formula builder panel on the right hand side for you

Excel DSUM Function Single Criteria 2

The three options that I have their database.

First thing the function needs to know is where’s your list.

What’s the range for your list.

Excel DSUM Function Single Criteria 3

I’m going to go select a one that’s the top of my list, I’m going to use some shortcut keys here, I’m going to do Ctrl + Shift + down arrow, that’ll highlight that column all the way down to the bottom. Then I’m going to do Ctrl + Shift + right arrow and that will highlight all of the data all the columns. I’ve got A1 to F59.

If you’re shortcut keys aren’t working for you, I love them but sometimes they don’t work, your keyboard set up is a little bit different, You can just type in the A1:F59 or you can click and drag to highlight that range as well. The important part is we get the database the whole list inside of that field.

The next thing it needs to know is the field that we ultimately want to SUM, in this case we want to sum the total sales column because we already selected the entire list.

The only thing the field needs is F1 that cell right there, total sales F1 where that’s not that total sales cell lives you can see down here field is either the label of the column, I just click on the cell and done.

The next thing that we need inside there is the criteria so we specified the database the lists. We’ve told that which column we want to sum and then the criteria is going to be this area.

Here are i2:i3 range that includes our header and the adjacent cell of i3 which contains your rent.

We don’t have the filter list there’s nothing else additional that we had to do list. then hit OK, and we get our total results our total sales based on the category of rent.