This add-in will help you filter and analyze results for long formulas using SumIfs, CountIfs, or AverageIfs functions, and it's FREE!
Video Tutorial
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?
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.
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…
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.
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.
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
- Once the add-in is installed, the “IFs Filter” button will be added to the Add-ins tab of the ribbon.
- Pressing this button opens the add-in window.
- Select any cell that contains an *IFS formula and press the “Filter Formula in Selected Cell” button.
- 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
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.
I receive runtime error’1004′
Auto Filter method of range class failed
when I run ifs filter. Please help.
Don
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.
[email protected]
Oh, this is cool. How have I missed this until now?
Thanks Jeff! I try to keep this stuff hidden 😉 I actually forgot about this one too.
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
Of course, that might work better if we defined strRange as string rather than as strubg. :-/
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
Hi Ahamed,
You might just need to close the form before selecting the cells. Let me know if that helps. Thanks!
Absolutely love this!!! Thanks Jon
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 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?
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
Thanks so much for your support Ganesh! I really appreciate you sharing and recommending Excel Campus. That really helps me out. 🙂
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…
I need excel files
I need help with a countifs formula. I am using this to return a number count of Unit Code. =IF(COUNTIFS($J$2:J2,J2)>1,0,COUNTIFS($B$1:B1,””&0)). What I get is numbering of 1, 2, 3 and so on for each new unit code. this works great for a ton of data lines when you are just needing the number of new unit code. What I am trying to do is use to data points. Count the number 1, 2, 3, 4 of Unit code and Name fields. I could share the file if that would help.
Hi
Do you have a utility I can convert all sumif to sumifs