This add-in will help you filter and analyze results for long formulas using SumIfs, CountIfs, or AverageIfs functions, and it's FREE!

Video

The Problem

The IFS functions are great for finding a result based on multiple criteria.  But the formulas can quickly grow in size and become difficult to understand.

Have you ever seen a formula that looks like this and wondered what it was calculating?

Long Excel SUMIFS Excel Formula Box

It's very difficult to quickly analyze this formula and know what criteria are being summed in the ‘Sales Data' worksheet.

This formula is actually summing the Qty column in the following table based on five different criteria for Rep ID, Region, Product, Color, and Size.

Long Excel SUMIFS Formula

This is easier to see if the formula is located on the same sheet as the source data (as shown above).  Most of the time you will have the summary calculations on a separate sheet from the source data, meaning you will have to flip back and forth between tabs to analyze and troubleshoot your formula.

The Functions Argument window can help…

SUMIFS Functions Argument Window

But often times I find myself manually filtering the source table for all the criteria so I can view the rows that are being summed.  This can become a very time consuming task when troubleshooting long formulas.

Manual Filter Sumifs Formulas

The Solution

To make this process faster I developed a simple add-in that automatically filters the source data based on the criteria in the SUMIFS, COUNTIFS, or AVERAGEIFS function.

Ifs Filter Add-in Screenshot

How it Works

When the “Filter Formula in Selected Cell” button in the add-in window is pressed, the add-in will filter the source data for all the criteria in the formula.  The Field Names and Criteria will also be listed in the listbox on the add-in window.

These are two great features that make it very fast and easy to see exactly what your formula is calculating.  This add-in has saved me a tremendous amount of time, especially when working with someone else's model where I am not familiar with the formulas.

Quick Guide

  1. Once the add-in is installed, the “IFs Filter” button will be added to the Add-ins tab of the ribbon.
  2. Pressing this button opens the add-in window.
  3. Select any cell that contains an *IFS formula and press the “Filter Formula in Selected Cell” button.  
  4. The source data will automatically be filtered based on the formula's criteria, and the fields and criteria will be listed in the listbox on the form window.

Open Source Project

The VBA project is unlocked and the code is free for you to manipulate.  It can definitely be enhanced further and I would like to hear your ideas and feature requests.

Please leave a comment at the bottom of the page with any questions or suggestions.

Download

IFs Filter Add-in.zip (862.1 KB)

The zip file includes the add-in file and installation instructions.  It is compatible with Excel 2007, 2010, and 2013.  The SUMIFS, COUNTIFS, and AVERAGEIFS functions were introduced in Excel 2007, so there is no need for compatibility with a prior version.

Feature Request List

Here are a few features I would like to add, and maybe this will spark some ideas for more.

  • Add a button to go back to the cell that contains the formula.
  • Display the cell address of the formula on the form (add-in window).
  • Add check boxes to the listbox so specific column filters can be cleared and reapplied.

Please leave a comment to vote for these features or request others.

14 comments

Your email address will not be published. Required fields are marked *

  • Hi, Jon
    It seems doesn’t work with tables. I have a message “You must turn on filter in your data source” but the table is with filters by default. And does it work with Excel 2019? I converted my table into a range to see how it works and I had a result but the source data was filtered totally – no rows with data.
    I can’t show the result here…

  • Your articles were written in very easily understandable manner. I would highly recommend your courses to colleagues, friends. Thank you very much for your valuable content. God bless you for helping excel community

  • Hi John,

    Love the filter but….
    I add sumifs together in the same cell and sometimes the source data is on different worksheets. The filter appears to be looking only at the first sumifs in the cell.

    Is it possible to have the filter look at all the sumifs in the cell?

  • Hello Jon
    I wonder if you can help me? I have created a number of spreadsheets to help my daughter in her role as Assessment Coordinator, but now I seem to have encountered an intractable problem. In cell R8 on worksheet 1a I would like to devise a formula to give me the number of boys who attained either a 2 or a 3 for reading. The formula needs to be based on a data table which includes columns for Reading (column C) and Gender (column M). In the Reading column the data is either 2 or 3 and in the Gender column the data is either M or F. Would it be possible to enter the appropriate result for the other eleven sheets, using the data specific to each sheet, in R8 on these sheets?

    Having tried Countif/s, Sumproduct and Sumifs I am running out of options and her deadline is rapidly approaching. I would welcome any help as I have laboured for over fifty hours and I do not want to let her down.

    Thank you in anticipation.
    Paul

  • Hi Jon,

    The IFs Filter works great, but what I noticed is.. after activating the filter from the selected cell, the filters are applied in the source data however, unable to select or copy the filtered data range.

    Any fix for this would be appreciated.

    Regards,
    Ahamed

  • Could you incoporate something like this in the addin to avoid autofilter errors?

    Sub set_filter_for_addin()
    Dim strRange As strubg
    If Sheets("name").AutoFilter = True Then
    strRange = Sheets("name").AutoFilter.Range.Address
    Sheets("name").AutoFilterMode = False
    End If
    Sheets("name").Range("your sumifs range ").AutoFilter
    End Sub

    Sub restore_filter()
    Sheets("name").AutoFilterMode = False
    Sheets("name").Range(strRange).AutoFilter
    End

    • Hi Don,

      Sorry to hear that you are getting that error message. This can happen if you have the Filter applied to a range on the worksheet that does NOT include the range in your SUMIFS formula.

      For example, your sheet might have a filter applied to the range E1:H100, but the SUMIFS formula references the range A1:C100.

      To solve this you will need to first remove the filter by pressing the Filter button on the Data menu (Ctrl+Shift+L). Then select the range that the SUMIFS formula references and apply the filter by pressing the Filter button on the Data menu.

      Please let me know if this works. You can also send me your file if you are still having troubles. I would be happy to take a look at it.

      jon@excelcampus.com

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly

Macros and VBA Training Webinar