Tom’s Tutorials For Excel: Summing The Intersection of Two Ranges
Here’s a way to structure a SUM function for operating on an intersection of rows and columns.
In the picture, there are row and column totals for the entire year’s Income budget. But suppose you only want to look at,
…
Tom’s Tutorials For Excel: Selecting Functions From the SUM icon’s Drop-Down List
As the pictures show, you can click the small drop-down arrow to the immediate right of the SUM icon for a list of other functions to select. As the “Version 2003” picture shows, clicking the More Functions item shows the Insert Function dialog box.
…
Tom’s Tutorials For Excel: Handling Multiple OR Lookup Criteria
When you are faced with a LOOKUP task and the possibilities require too many nested “OR” operations, consider an array constant in your formula.
In the picture, a company is grouping its employees by the first initial of their last name.
…
Tom’s Tutorials For Excel: Numbering a Dynamic List of Filled Items
For situations such as lists that change often, with blank cells between topics that must be clearly indexed or numbered, here’s a formula that can do the job.
In the picture, a list of Excel training topics is being compiled.
…
Tom’s Tutorials For Excel: Summing to Today’s Date With INDIRECT
Here’s an example of using the INDIRECT and SUM functions to add numbers from the top of column B to the row where today’s date (or whatever date you specify) is found in column A.
The formula in cell E2 is
=SUM(INDIRECT(“B1:B”&MATCH(E1,A:A,0)))
If you want to bypass the helper cell for today’s date,
…
Tom’s Tutorials For Excel: SUMIF For Days Past Last Date
Here’s an example of two formulas working in tandem for a common goal.
In cell E1 is the formula
=LOOKUP(9.99999999999999E+307,A:A)
which returns the last number (in this case a date) in column A.
…
Tom’s Tutorials For Excel: Horizontal SUMPRODUCT
Just a quick example of the SUMPRODUCT function being utilized for data arranged horizontally across a row, instead of the more usually-seen columnar arrangement.
The formula in cell B12 is =SUMPRODUCT((A7:L7=B10)*(A8:L8=B11))
The formula in cell B23 is =SUMPRODUCT((A18:L18=B21)*(A19:L19=B22))
…
Tom’s Tutorials For Excel: Ranking With Conditional Omission
Here’s an example of ranking a list while omitting an item for consideration. In blue cell E2 is the name of an employee to eliminate from consideration in the ranking process.
The formula in cell C5 and copied down to cell C20 is
=IF(A5$E$2,SUMPRODUCT(($A$5:$A$20″”)+0,(B5
…
Tom’s Tutorials For Excel: Fuzzy Matching With Partial Strings For Sum or Average
When you have a mish-mash of alphanumeric strings in a list, here’s how you can sum or average their corresponding numbers based on a partial string criterion.
The picture shows two examples with the criterion in cell B4.
…
Tom’s Tutorials For Excel: Summing Sets of Identical Items
Here’s a formula method to subtotal numbers in one column for each set of items in another column.
A company’s quarterly sales are listed, with varying salespeople each quarter. At each change in quarter in column A,
…
Tom’s Tutorials For Excel: SUMIF formula examples
Here are examples of formula constructions with the SUMIF function.
When you want to refer to a cell in the formula, enclose the mathematical operators in quotes.
In the pictured Example 1, the formula is =SUMIF(A2:A30,”=”&C1,B2:B30)
In the pictured Example 2,
…
Tom’s Tutorials For Excel: Converting All Formula Cell References From Relative to Absolute
When you have a lot of formulas on a worksheet for which you want to convert all cell and range references from relative to absolute, this macro can do the job:
Sub ConvertRelativeToAbsolute()
Dim cell As Range,…
Tom’s Tutorials For Excel: Keep a Running Count of Duplicate Entries
In cases where duplicate entries are expected, you can easily keep a running count of each entry with a formula to tell you how many times the entry exists in your list. There is a number next to each entry to tell you how many times in the list that particular entry has thus far appeared.
…
Tom’s Tutorials For Excel: Make Your Dates Readable When Combined With Text
When combining dates with words in a formula, dates don’t always look like dates in the result.
For example, the above picture shows a date in cell A1.
…
Tom’s Tutorials For Excel: Summing Time
In the pictures, the formula in cell B3 is =B2-B1 which returns the elapsed time in hours and minutes between the Start time and the End time. You want the visual value 6:45 (meaning six hours and forty-five minutes), but instead Excel gives you an elapsed time that looks like a time of day,
…
Tom’s Tutorials For Excel: Calculating Salary Overtime
The picture shows how you can set up a worksheet to calculate weekly salaries for your employees, considering hourly wages and overtime hours.
The formula in cell D5 that was copied down to cell D12 is
=IF(B5
…
Tom’s Tutorials For Excel: The INT Function
The INT function rounds a number down to the nearest integer.
Example, in cell A1 is 2.7. In cell B1, the formula =INT(A1) returns 2.
For negative numbers, INT returns the next number away from zero.
…
Tom’s Tutorials For Excel: Reverse Intersect Lookup for Headers
I recently posted this example, followed by this more advanced example for finding an intersecting value in a table with multiple rows and columns.
Today it’s the opposite scenario, where you identify a value in the table and you lookup the value’s row header,
…
Tom’s Tutorials For Excel: The INDIRECT Function
The INDIRECT function returns a range reference based on a text string, which can return a value associated with that range reference. INDIRECT is a very versatile function, best explained by examples as shown below.
Example 1
A simple example to get started: the formula in cell A3 is =INDIRECT(B2).
…
Tom’s Tutorials For Excel: The NETWORKDAYS Function
In many business situations, days must be counted as “business days” rather than as “calendar days” to represent the actual non-weekend, non-holiday days that have passed between two dates. For this purpose, the NETWORKDAYS function is perfect.
In the next picture,
…
Tom’s Tutorials For Excel: Summing Cells Across Worksheets
Here are formulas that sum numbers in a single cell, and in a range of cells, across multiple worksheets. The first picture shows a section from four different worksheets, each displaying Income and Expenses for their respective Quarter.
…
Tom’s Tutorials For Excel: More Date Formulas
Following up on yesterday’s theme, here are more common scenarios involving date calculations. Referring to the picture…
…The formula in cell B3 that is copied across to cell G3 for last day of month, first day of next month,
…
Tom’s Tutorials For Excel: Date Formulas
Here are a few date formulas for common situations. Referring to the picture…
…The formula for text and date in cell C1 is
=”Today’s date is “&A2
The better formula in cell C2 is
=”Today’s date is “&TEXT(A2,”MMM DD,
…
Tom’s Tutorials For Excel: Match From List
The below picture shows one way to see if any member of a collection of smaller strings (what I call “Key Codes”) are found in a much longer Product Code string.
The formulas in the yellow cells search the entire Key Code range of A4:A8.
…
Tom’s Tutorials For Excel: The NPV (Net Present Value) Function
Excel’s NPV (Net Present Value) function calculates the sum of a series of net cash flows, each of which has been discounted to the present using a static discount rate. Future payments are regarded as negative values,
…
Tom’s Tutorials For Excel: Sorting The Unsortable
Sometimes you need to sort a list with varying hierarchy logic. For example, Picture #1 shows how “Section” is listed in column A with numbers in the same cell to indicate an order from 1 to 20.
If you try to sort that list in ascending order,
…
Tom’s Tutorials For Excel: Advanced Filter Calculated Criteria
Advanced Filter is among Excel’s most powerful and versatile tools. This example shows how a formula can serve as the criterion for filtering a table, based on a separate list of criteria items.
In Picture #1,
…
Tom’s Tutorials For Excel: Conditional Boolean COUNT, SUM, and AVERAGE
Here’s how you can perform mathematical operations when dealing with Boolean (TRUE or FALSE) values in a cell, for example to return count, sum and average values.
Notice in the formulas that the TRUE and FALSE criteria are not enclosed in double quotes.
…
Tom’s Tutorials For Excel: Rolling Averages
Sometimes you want to know an average of the most recent instances of a recurring item. For example, some companies base their retirement pension plan on the average of an employee’s last three years of annual salary.
In this example,
…
Tom’s Tutorials For Excel: LOOKUP By MIN or MAX
You can do a lookup based on other worksheet functions such as MIN and MAX.
In Picture #1, the formula =INDEX(A2:A11,MATCH(MIN(B2:B11),B2:B11,0)) returns Isabella because her Sales Dollars number of $5,012 is the lowest (minimum) number in column B.
…
Tom’s Tutorials For Excel: COUNTIF and SUMIF Anything or Nothing
You can use the “” and “=” operators with worksheet functions for identifying the presence or absence of values in a cell.
In Picture #1, the formula in cell E4 is =COUNTIF(A2:A10,””).
The number 6 is returned because 6 items appear in range A2:A10.
…
Tom’s Tutorials For Excel: Lookup Oldest and Newest Date
The picture shows one way of obtaining the oldest and newest dates in column B that correspond to the person’s name in column A that is in cell D2 as a lookup criteria. Because dates are numbers,
…
Tom’s Tutorials For Excel: Parse a Cell’s Contents
If you ever need to parse a cell’s contents such that each character is displayed along the row in its own cell, here’s one way to do it.
In the picture,
…
Tom’s Tutorials For Excel: First and Last Values Along a Row
When you are faced with a table that is scattered with values among empty cells, you might need to know the first (left-most) or last (right-most) value in that row.
In the following picture,
…
Tom’s Tutorials For Excel: LOOKUP and ADDRESS of the Last Instance
Sometimes you have a list of items that are repeated many times. You might only want information about the last entry of a particular item, such as its cell address or some lookup information in its adjacent cell.
…
Tom’s Tutorials For Excel: COUNTIF and SUMIF with wildcards
You can use wildcard characters with functions to return specific information. For example, the wildcard “?” character represents any single character. The wildard “*” character represents any count of characters.
To see wildacrds in action,
…
Tom’s Tutorials For Excel: Ranking With Ties
You may have come across situations where you need to rank a series of numbers, such as a list of test scores or elapsed times in a footrace. The RANK function does a good job of indicating the scores in order of first,
…
Tom’s Tutorials For Excel: Transform a Range Into a Single Column List
Here’s how you can take a range of data comprised by multiple columns and rows, and list its items in a single column.
In the picture, all the items in range A1:D5 are listed in column F with the formula
=INDEX($A$1:$D$5,ROWS($1:4)/4,MOD(ROWS($1:1)-1,4)+1)
that is entered into cell F1 and copied down as far as you’d need to in order to capture all the cells in the data range.
…
Tom’s Tutorials For Excel: Find Your Address
Suppose you have unique items in column C such as a list of the world’s major cities. If you want to find the cell address of a particular city, say London for example, this formula will return $C$4 as shown in the next picture.
…
Tom’s Tutorials For Excel: Subtotaling Filtered Data
With the SUBTOTAL function you can perform mathematical operations on visible filtered data.
The SUBTOTAL function wants two pieces of information from you:
(1) The type of operation (SUM, AVERAGE, and so on) you want to perform.
…
Tom’s Tutorial For Excel: SUMIF for Multiple Criteria
The SUMIF function handles one criteria argument, which is great if you only need to sum for one criteria. But what if you want to sum for two or more criteria?
In the picture,
…
Tom’s Tutorial For Excel: Summing For Multiple Conditions
When you need to sum, average, or analyze data based on more than one condition, the SUMPRODUCT function is an excellent tool for the job.
The pictured table shows an example of sales activity in the widget industry that includes several different regions,
…
Tom’s Tutorial For Excel: TRIM and CLEAN your data
Sometimes you will receive output files from data warehouse applications, or you will copy data from a web page onto your Excel worksheet. Many cells can look jumbled with the data they hold, because of extra (and usually invisible) spacebar or ASCII characters that were not meant to be a part of the actual data.
…
Tom’s Tutorials for Excel: Count and Sum Between Dates
In the picture, a table contains several columns of data, including a column of dates. Three examples show how you can perform various mathematical operations on multiple-column criteria between dates using the SUMPRODUCT function.
…
Tom’s Tutorials for Excel: Reverse Lookup
As you probably know, the VLOOKUP function searches the first column of a range of cells and returns a value from a cell in the same row. But what if your criteria item — that is, what you are trying to look up —
…
Tom’s Tutorials for Excel: Count Your Workbook’s Formulas
Here is a macro that goes sheet by sheet through your workbook, and shows you a Message Box with the count how many formulas (if any) are on each sheet. Substitute your sheet’s actual password with the sample “YourPassword”
…
Tom’s Tutorials for Excel: List all link formulas’ locations and their source workbooks, sheets, and cells.
Tom Urtis gives us another one.
For link formulas in your workbook, you can list their sheet and cell locations, AND the name of their precedent source workbook,
…
Tom’s Tutorials for Excel: Lookup Intersecting Value by Row and Column Criteria.
Here’s another one of Tom Urtis’ nifty tips in a quick format. Enjoy & Excel!
Using two criteria (one as row header label and the other as column header label), an intersecting value is returned,
…
Tom’s Tutorials for Excel: How old are you? Undocumented DATEDIF.
Tom Urtis’ “Tom’s Tips for Excel” are being read so widely we are trying to share more of them, quick ones, longer ones & illustrated ones, each week. We welcome your comments & feedback. Do you prefer certain types of tips?
…
Tom’s Tutorials for Excel – Locating the Last Value in a List. Tom Urtis latest tip for you. Happy midsummer! Get ready for fun facts & quick tips as the weekend approaches.
The picture shows a list of numeric and text values in column A.
Last number
The last number in the list is returned by this formula in cell D2:
=LOOKUP(9.99999999999999E+307,A:A)
The row of the last number in the list is returned by this formula in cell E2:
=MATCH(9.99999999999999E+307,A:A)
The address of the last number in the list is returned by this formula in cell F2:
=ADDRESS(MATCH(9.99999999999999E+307,A:A),1)
Last text
The last text value in the list is returned by this formula in cell D3:
=INDEX(A:A,MATCH(“*”,A:A,-1))
The row of the last text value in the list is returned by this formula in cell E3:
=MATCH(REPT(“z”,255),A:A)
The address of the last text value in the list is returned by this formula in cell F3:
=ADDRESS(MATCH(REPT(“z”,255),A:A),1,1)
Last of either number or text (that is,
…
San Francisco, California
1-925-310-4170
tom@atlaspm.com
