Thursday, September 23, 2021
In Excel Tables, you can filter on any two conditions in a column. But by using the SUMPRODUCT function, you can filter on any number of items in a list.

How to Use SUMPRODUCT in an Excel Table to Filter Any Number of Items

Excel 2007 introduced the powerful Table feature, as illustrated below. Tables allow you to sort and filter your data easily. However, the filter capability has...
Although Excel provides two worksheet functions that ignore filtered rows in a Table, nearly any function can ignore those hidden rows if you use this new trick.

Use a ‘Visible’ Column in Formulas to Ignore Hidden Rows in Filtered Tables

Excel Tables, introduced in Version 2007, give us the ability to use column filters to hide rows in a Table. And slicers for Tables, introduced...
Here are the only two ways I know to set up formulas that look up data in an Excel Table, using more than one criteria.

Two Ways to Set Up Multi-Criteria Lookup Formulas in Excel

The Excel Table below illustrates a common type of lookup problem…perhaps taken to a slight extreme. Here, we have a specific manager for each month...

How to Use Advanced Filters to Create Unique List of Items from an Excel...

(Note: Filters have changed a lot since this page was written. It's scheduled for a complete makeover.) When we work with data in an Excel...
Do you need to update your Excel reports with daily, weekly, or monthly data? Here's a low-maintenance way to do it, using one type of Excel database.

How to Report Periodic Data from Excel Databases

Sometimes, the best way to solve your Excel problem is to redesign your workbook. Ron G. brought this thought to mind with a recent question....
Here's how a few Excel formulas can move data between Excel's most widely used table designs to transfer data from source files into a more usable form.

How to Read and Update Excel Tables Using SUMIFS and INDEX-MATCH

A growing number of Excel users in business are linking their reports and analyses to Excel tables. By doing so, they can update them...
Here's how to use formulas and conditional formatting to expand or contract your Excel report automatically, without macros, when you change a cell value.

Create Variable-Length, Dynamic Reports Linked to Excel Tables

Excel Tables are a powerful feature introduced in Excel 2007. Not only can you report from them directly, you can use them as a...
Pivot Tables aren't merely a way to interact with your data. You also can use them as a rich source of data for standard reports and analyses. And you don't need to limit formulas to GETPIVOTDATA; you also can use SUMIFS, SUMPRODUCT, and all other Excel functions with pivots. Here's how.

How to Set Up a Pivot Table as an Excel-Friendly Database

You can use a Pivot Table as a database in the same way that you can use Excel Tables and other Excel-Friendly Databases (EFDs). In...
Excel Tables offer the ability to filter on two criteria in one column. Here one way to search for many criteria in a Table.

How to Add Advanced Filter Capabilities to Excel Tables

One useful feature of Excel Tables is the ability to filter any number of columns. The filter control for each column allows us to...
SUMPRODUCT is one of Excel's most-powerful worksheet functions. Here, for example, you can use it in one formula to search text in one cell for many items.

How to Perform Multiple Table Searches Using the SEARCH & SUMPRODUCT Functions in Excel

SUMPRODUCT is one of Excel's most-powerful worksheet functions. Here, for example, you can use it in one formula to search text in one cell...

Latest Articles

Two unusual formulas can use the SEARCH or FIND functions to search any number of cells for any number of words—almost instantly.

How I Set Up SEARCH and FIND Formulas on Steroids

I've used Excel long enough to recognize when I've stumbled across an unusual formula. And it happened again a few minutes ago. In the past,...
Use this unique Excel dashboard to track the general rate of inflation, your personal rate, and the rate of the goods and services you buy.

Inflation Tracker Excel Dashboard with Consumer Prices

U.S. President John F. Kennedy once said of a growing economy, "A rising tide lifts all boats." However, a rising inflationary tide does not lift...
Excel's Quick Access Toolbar can save you many clicks and needless work. Here are the eight QAT tools that I use, and how I use them.

Eight Great Tools for Your Quick Access Toolbar

I use eight great tools in my Quick Action Toolbar (QAT), and you might want to consider using them yourself. Before I get into the...
Advertisement
Advertisement