Excel SUMIF function – formula examples to conditionally sum cells This tutorial explains the Excel SUMIF function in plain English and provides a numbers of SUMIF formula examples for numbers, text, dates and wildcards. If you are faced with the task that requires conditional sum in Excel, the SUMIF function is what you need. This tutorial will briefly explain the function's syntax and general usage, and then you will extend the new knowledge in practice with a number of SUMIF formula examples. A good thing is that the SUMIF function is identical in all Excel versions, from 2013 to 2003. SUMIF in Excel - syntax and usage The SUMIF function, also known as Excel conditional sum, is used to add cells based on a certain condition, or criteria. If you've happened to read the COUNTIF tutorial on this blog, you won't have any difficulties with understanding Excel SUMIF because its syntax and usage is analogous. SUMIF(range, criteria, [sum_range]) As you see, the SUMIF function has 3 arguments - first 2 are required and the 3rd one is optional. Note. Note. Note.
Delete Blank Rows in Excel, Remove Blank Cells in Excel Posted on January 26th, 2010 in Learn Excel - 114 comments Blank rows or Blank cells is a problem we all inherit one time or another. This is very common when you try to import data from somewhere else (like a text file or a CSV file). Today we will learn a very simple trick to delete blank rows from excel spreadsheets. Select your dataPress F5 This opens “Go to” dialog in Excel. Now hit on that “select” button.From “select special” screen, select “Blanks” (shown aside) Now, all the blank cells will be selected.Just press CTRL and Minus sign (-)Select “shift cells up” or “entire row” as needed. That is all. Bonus tip: If you are looking for keyboard short-cut for this, here it is. F5 ALT+s k Enter CTRL+ – u Enter Remove Blank Rows in Excel – Video Here is a short video showing this in action. (watch it on youtube) Browse more quick tips. Share this tip with your friends
Excel Tutorial - Easy Excel 2010 Nested IFs A well known limitation to Excel is that you cannot "nest" more than 7 functions. For example, the following formula will fail because the limit is exceeded. =IF(Sheet1! As a general "rule of thumb," if you have a formula with more than 7 nested statements, you should consider using a VBA function instead. Suppose we wanted an nested IF formula to test: IF A4 = 1 Then 11 Else If A4 = 2 Then 22 Else If A4 = 3 Then 33 Else If A4 = 4 Then 44 ... =IF(Sheet1! Then create another named formula called SevenToThirteen, referring to the formula: =IF(Sheet1! Finally, enter the "master" formula in the worksheet cell: =IF(OneToSix,OneToSix,SevenToThirteen) This "beats" the nested function limitation because no single part of the formula exceeds the limit, even though the "sum" of the components do. You can use this technique whenever you exceed the limit on nested functions.
Excel Formulas- free tutorial How to use nested IF statements in Excel with AND, OR, NOT | Experiments in Finance Following up on last week’s introductory post on using the IF function, here’s one on how to use Excel’s IF statement when you have multiple conditions to meet. The original question I received from a reader, which prompted this series, was about how to use the IF statement in Excel when you had two conditions that had to be met. For example, when sales fell between a minimum and maximum number. Let’s take a look at our example again. Note that I’ve added the maximum amount of $200K into cell B3: Before we go further, if you’d like to work through the examples yourself, here’s the raw data you can copy into an Excel worksheet. Ok, now back to the tutorial. Now, let’s suppose sales have to be greater than or equal to $100K and less than $200K for a salesman to receive a 12% commission rather than just be greater than $100K, as in our introductory example. It turns out that you can use Excel’s AND function, which Excel calls a logical operator (just like it calls the IF function).
Excel COUNTIFS and COUNTIF with multiple criteria – examples of usage The tutorial explains how to use COUNTIFS and COUNTIF formulas with multiple criteria in Excel. You will find a number of examples for different data types - numbers, dates, text, wildcard characters, non-blank cells and more. Of all Excel functions, COUNTIFS and COUNTIF are probably most often mixed up because they look very much alike and both are purposed for counting cells based on the specified criteria. The difference is that the COUNTIF function is intended for counting cells based on a single condition in one range, while COUNTIFS allows using several criteria and ranges. Excel COUNTIFS function - syntax and usage As I've already mentioned, the Excel COUNTIFS function is purposed for counting cells across multiple ranges based on one or several conditions. COUNTIFS syntax The syntax of the COUNTIFS function is as follows: COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…) In fact, you don't have to remember the syntax of the COUNTIF function by heart. Example 1.
excel - Simple Pivot Table to Count Unique Values Excel Formulas: Defining and Creating a Formula In this lesson, we introduce you to basic rules for creating formulas and using functions. We feel one of the best ways to learn is through practice, so we provide several examples and explain them in detail. The topics we will cover include: rows and columnsexample math function: SUM()operatorsoperator precedenceexample financial function: PMT(), loan paymentusing a “string” function (“string” is shorthand for “string of text”) inside a formula and nesting functions Formulas are a mixture of “functions,” “operators,” and “operands.” Before we write a few formulas, we need to create a function but before we can create a function, we first need to understand row and column notation. Rows and Columns To understand how to write formulas and functions, you need to know about rows and columns. Rows run horizontally and columns run vertically. Columns are labeled by letters; rows by numbers. Example: Function Sum() Now let’s demonstrate how to use a function. #NAME? Calculation Operators
Insert a linked Excel chart in PowerPoint 2010 - PowerPoint You can insert and link a chart from an Excel workbook into your PowerPoint presentation. When you edit the data in the spreadsheet, the chart on the PowerPoint slide can be easily updated. For more information about how to create a chart in Excel 2010, see Quick start: Chart your data. For more information about how to create an embedded chart in PowerPoint 2010, see Quick start: Add a chart to your presentation. To insert a linked Excel chart in PowerPoint 2010, do the following: Open the Excel workbook that has the chart that you want. Notes The workbook must be saved before the chart data can be linked in the PowerPoint file. Tip When you want to update the data in the PowerPoint file, select the chart, and then under Chart Tools, on the Design tab, in the Data group, click Refresh Data. Top of Page
Apply data validation to cells - Excel You can use data validation to restrict the type of data or the values that users enter into a cell. One of the most common data validation uses is to create a drop-down list. Check out this video by Doug from Office as he gives a quick data validation overview. Download our examples You can download an example workbook with all of the data validation examples you'll find in this article. Download Excel data validation examples Add data validation to a cell or a range Note: The first three steps in this section are for adding any type of data validation. Select one or more cells to validate. The best thing about using a table is that as you add or remove items from your list, your data validation list will update automatically. Notes: After you create your drop-down list, make sure it works the way you want. Adding other types of data validation The following table lists other types of data validation and shows you ways to add it to your worksheets. Examples of formulas in data validation
Between Formula in Excel - How to check if a value falls between 2 other values using Excel Formulas? Posted on June 24th, 2010 in Excel Howtos - 135 comments In today’s quick tip, lets find how to check for between conditions in Excel using formulas, like this: Between Formula in Excel for Numbers: Lets say you have 3 values in A1, A2 and A3. Now, the simplest formula for such a thing would be test whether the conditions A1>=A2, A1<=A3 are both true. However, there are 2 problems with a formula like above: 1. Shouldn’t there be a shorter and simpler formula?!? Well, there is. =if(A1=MEDIAN(A1:A3),"Yes","No") Now, not only does the above formula look elegant and simple, it also works whether A2 is smaller or larger than A3. Between Formula in Excel for Dates: Well, dates are just numbers in Excel. Between Formula for Text Values: Lets say you want to find-out if the text in A1 is between text in A2 and A3 when arranged alphabetically, a la in dictionary. Between Formulas in Excel – Summary and Examples: Do you check for Between Conditions in Excel? Share using comments.
Create and manage drop-down lists - Excel You can make a worksheet more efficient by providing drop-down lists. Someone using your worksheet clicks an arrow, and then clicks an entry in the list. Take this course to learn more about how best to use and manage them. Drop-down lists (1:01) Data entry is quicker and more accurate when you use a drop-down list to limit the entries people can make in a cell. When someone selects a cell, the drop-down list’s down-arrow appears, and they can click it and make a selection. Drop-down list settings (4:37) You can use a comma-delimited list, a cell range, or a named range to define the options in a drop-down list. Input and error messages (2:47) To help people decide what drop-down list option to select, and even to let them know that a cell contains a drop-down list, you can create a message that appears when they select a cell. Course summary A brief reminder of the key points in this course. More courses available at Microsoft Office Training.