Blog Archives

Tom’s Tutorials For Excel: Summing The Intersection of Two Ranges

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,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Selecting Functions From the SUM icon’s Drop-Down List

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.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Handling Multiple OR Lookup Criteria

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.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Numbering a Dynamic List of Filled Items

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.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Summing to Today’s Date With INDIRECT

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,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: SUMIF For Days Past Last 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.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Horizontal SUMPRODUCT

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))

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Ranking With Conditional Omission

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

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Fuzzy Matching With Partial Strings For Sum or Average

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.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Summing Sets of Identical Items

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,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: SUMIF formula examples

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,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Converting All Formula Cell References From Relative to Absolute

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,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Keep a Running Count of Duplicate Entries

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.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Make Dates look Familiar When Combined With Words In A Formula

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.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Summing Time

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,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Calculating Salary Overtime

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

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: The INT Function

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.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Reverse Intersect Lookup for Headers

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,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , ,

Tom’s Tutorials For Excel: The INDIRECT Function

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).

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , ,

Tom’s Tutorials For Excel: The NETWORKDAYS Function

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,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Summing Cells Across Worksheets

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.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: More Date Formulas

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,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Date Formulas

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,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Match From List

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.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: The NPV (Net Present Value) Function

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,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Sorting The Unsortable

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,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Advanced Filter Calculated Criteria

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,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Conditional Boolean COUNT, SUM, and AVERAGE

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.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Rolling Averages

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,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: LOOKUP By MIN or MAX

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.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: COUNTIF and SUMIF Anything or Nothing

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.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Lookup Oldest and Newest Date

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,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Parse a Cell’s Contents

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,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: First and Last Values Along a Row

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,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: LOOKUP and ADDRESS of the Last Instance

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.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: COUNTIF and SUMIF with wildcards

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,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Ranking With Ties

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,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Transform a Range Into a Single Column List

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.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Find Your Address

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.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Subtotaling Filtered Data

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.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: SUMIF With Multiple Criteria

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,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Summing For Multiple Conditions

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,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials for Excel: TRIM and CLEAN your data

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.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials for Excel: Count and Sum Between Dates

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.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials for Excel: Reverse Lookup

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 —

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials for Excel: Count Your Workbook’s Formulas

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”

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials for Excel: List all link formulas’ locations and their source workbooks, sheets, and cells

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,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials for Excel: Look up Intersecting Value by Row and Column Criteria

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,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials for Excel: How old are you? Undocumented DATEDIF

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?

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials for Excel: Locating the Last Value in a List

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,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,