Tuesday, April 13, 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

Using Excel's LET, SORTBY, and SEQUENCE functions, and dynamic arrays, you easily can list any number of top and bottom results from a Table.

How to Report Top and Bottom Results Using Dynamic Arrays in Excel

In How to Use Excel’s LET Function, I showed several examples of a powerful function that Microsoft added to Excel 365 in the summer...
The LET function is the most powerful function that Microsoft has released for Excel in years. Here's an introduction to its features.

How to Use Excel’s LET Function

In the summer of 2020, Microsoft introduced the LET function for Excel 365—one of the most-significant new worksheet functions that Microsoft has introduced in...
You can ratchet down errors in your Excel reports by using an Error Summary Table that uses conditional formatting to alert you to errors.

How to Set Up an Automatic Error-Checking System in Excel Reports

Decades ago, I worked as a cost accountant for a large company. But because our department received terrible reports, I wrote my own reports...using...
Advertisement
Advertisement