SUMIFS, COUNTIFS, AVERAGEIFS Filter & Analyzer Add-in - Excel Campus
10

SUMIFS, COUNTIFS, AVERAGEIFS Filter & Analyzer Add-in

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.

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 10 comments
Paul - August 7, 2017

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

Reply
Spencer - September 19, 2016

Absolutely love this!!! Thanks Jon

Reply
Ahamed - May 18, 2016

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

Reply
    Jon Acampora - May 21, 2016

    Hi Ahamed,
    You might just need to close the form before selecting the cells. Let me know if that helps. Thanks!

    Reply
Kat - June 19, 2015

Of course, that might work better if we defined strRange as string rather than as strubg. :-/

Reply
Kat - June 19, 2015

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

Reply
Jeff Weir - March 31, 2015

Oh, this is cool. How have I missed this until now?

Reply
Don - April 20, 2014

I receive runtime error’1004′
Auto Filter method of range class failed

when I run ifs filter. Please help.

Don

Reply
    Jon Acampora - April 20, 2014

    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

    Reply

Leave a Reply: