Posts

where things is the named range G5:G7. The result is $19.50, the sum of Price where Item is “Apples”, “Pears”, or “Kiwis”. SUMIFS with SUMPRODUCT One way to accomplish this is to give the SUMIFS function all three values in the named range things (G5:G7) as criteria, then use the SUMPRODUCT function to calculate a total. This is the approach in the worksheet shown, where the formula in I5 is: Working from the inside out, the SUMIFS function takes three arguments: sum_range, range, and criteria....

which returns 12, the total cells in the range B5:C10. The ROWS and COLUMNS functions can work with any sized rectangular range. For example, the formula below uses the ROWS function to count all rows in the column A, and the COLUMNS function to count all columns in row 1: ROWS returns a count of 1,048,576 and COLUMNS returns a count of 16,384, to the final result is 17,179,869,184....

When working with VBA, a variable is a location in your computer’s memory where you can store data. The type of data you can store in a variable would depend on the data type of the variable. For example, if you want to store integers in a variable, your data type would be ‘Integer’ and if you want to store text then your data type would be ‘String’. More on data types later in this tutorial....

There are many situations in which you may want to use logical criteria to filter or limit the values processed by the UNIQUE function. In this first worksheet, we have a list of values, some of which are duplicates. Notice the list also contains empty or blank cells. If I use the UNIQUE function on the data as-is, we do get unique values, but we also get a zero, since the source data contains empty cells....

VLOOKUP retrieves the correct building for each employee from Sheet2 into the table on Sheet1. On Sheet1, we retrieve the building location for each team member using this formula: The lookup value is the employee ID, from cell B5. For the table array, we use the range $B$5:$C$104 qualified with a sheet name, and locked as an absolute reference, so that the range does not change as the formula is copied down: This is the only difference from a normal VLOOKUP formula – the sheet name simply tells VLOOKUP where to find the lookup table range, B5:C104....

Pivot charts let you rapidly analyze large amounts of unsummarized data in different ways. Unlike normal charts, Pivot charts can be used to plot data with hundreds or thousands of rows. For example, on this worksheet, I have order data from a wholesale chocolate company over a period of 2 years, in almost 2000 rows of data. To introduce the idea of a pivot chart, I’ll first create a normal chart....

In this case, we want to find out of a particular sheet exists in a workbook, so we construct a full reference by concatenating the sheet names in column B with an exclamation mark and “A1”: This returns the text: which goes into the INDIRECT function. INDIRECT then tries to evaluate the text as a reference. When INDIRECT succeeds, the reference is passed into ISREF which returns TRUE. When INDIRECT can’t create a reference, it throws a #REF error, and ISREF returns FALSE....

You have a list of names and it’s all lower case letter. You need to fix them so they are all properly capitalized. With hundreds of names in your list, it’s going to be a pain to go through and edit first and last names. Thankfully, there are some easy ways to change the case of any text data in Excel. We can change text to lower case, upper case or proper case where each word is capitalized....

You may find that product references consist of a product code, code reference, and product size all concatenated into one piece of text which appears in one cell within the worksheet. Your requirement is that you want to split the relevant sections of the text string so that they all appear in individual cells in your worksheet. This may be because you want to sort or group by a particular section of text....

When you create a table, Excel will give it a generic name such as Table1, Table2, etc. But this isn’t usually a very good descriptive name for your table. You will need to reference your table name in formulas and other locations, so giving it a descriptive name is the best practice. Get your copy of the example workbook to follow along with the post and find out how to give your table a new name!...

When performing any data analysis in Excel you will often want to know the number of distinct items in a column. This statistic can give you a useful overview of the data and help you spot errors or inconsistencies. This post will show you all the ways you can count the number of distinct items in your list. Get your copy of the example workbook in this post to follow along....

Prominent at the top of the Excel window, the ribbon is a kind of super toolbar. The ribbon contains every command you’ll ever need in Excel, grouped by logical function into tabs. It’s a great way to explore what Excel can do. Let’s take a look. The ribbon is divided into tabs. Each tab contains logically grouped commands, further sub-divided into Groups. In some cases, there are more commands than available space, and you’ll see a small arrow in the lower right....

In an earlier video, I showed how AND logic corresponds to multiplication and OR logic corresponds to addition. Let’s look at how to apply this in an array formula. In the first worksheet, we want to sum orders where the state is Texas or “tx” and the amount is greater than 125. I’ll work through the problem step-by-step. In column H, I’ll test if state is equal to “tx”. In column I, I’ll test if the amount is greater than 125....

where “key” is the named range B5:C9. Note: the lookup table must be sorted in ascending order, and VLOOKUP must be set to approximate match mode. In approximate match mode VLOOKUP assumes the table is sorted by the first column. With a score provided as a lookup value, VLOOKUP will scan the first column of the table. If it finds an exact match, it will return the grade at that row....

The most satisfying and powerful thing you can do with Excel is to model a real-world problem in a way that helps you make a better decision. For a model to be effective, it needs to expose inputs in a way that makes sense, and generate results that are easy to interpret. In this article, we’ll use the seldom-seen Data Table to build a simple model that helps you visualize the profitability of a Google Adwords campaign....

How is the course delivered? This course is delivered as videos streamed directly to your computer, 24 x 7. Each video is short – typically 3 minutes or less – and all videos come with a practice worksheet that you can download directly below the video. The course is is self-paced, so can move through it as fast or slow as you like. Will the course work for both Windows and Mac?...

Let’s take a look. The easiest way to apply conditional formatting is to apply rules directly to the cells you want to format. For example, if I want to highlight the average test scores in column H that are below 80, I can just select the cells and apply a Conditional Formatting rule with that logic. But what if you want to highlight the names in column B when the average is below 80 in column H....

Background Sometimes Excel ends up with text in a cell, when you really want a number. There are many reasons this might happen, and many ways to fix. This article describes a formula-based approach convert text values to numbers. The VALUE function will try to “coerce” a number stored as text to a true number. In simple cases, it will just work and you’ll get a numeric result. If it doesn’t work, you’ll get a #VALUE error....

This formula returns a number that Excel recognizes as 2:00 AM. We add the result to the starting time: To make sure we have a true time value, we need to ensure that we have only a decimal value. In other words, if we add 12 hours (.5) to 6 PM (.75) we’ll get 1.25, but we really only want .25. To make sure we get just the decimal value, we use the MOD function with a divisor of 1, as a clever way to keep the formula simple....

The result in D5 is 2, since there are two numbers in B5. Create the array The first step is to split the text string into an array of characters. This is done with MID, LEN, and SEQUENCE like this: In a nutshell, the LEN function returns the length of the text in B5, which is 9, to the SEQUENCE function as the rows argument. SEQUENCE then generates an a numeric array like {1;2;3;4;5;6;7;8;9}, which is returned to the MID function as the start_num argument: The MID function then extracts each of the 9 characters and returns an array like {“1”;“8”;" “;“a”;“p”;“p”;“l”;“e”;“s”}....