Add/Remove Criteria in a COUNTIFS Statement

[Darkenergyz]

I am trying to figure out how to add/remove criteria to a COUNTIFS statement, based on the value of a VBA UserForm Checkbox.

All of the criteria for the checkboxes are based out of the same collum. Here is the similar code I currently utilize for my checkbox filter.

(Thank you RudiS)

Please Login or Register  to view this content.

The code hides fields within a collumn based on the tag within the checkbox's properties. Now I am trying to create an addition that will add criteria to a CountIf formula in real time.

An example would be

Please Login or Register  to view this content.

This one is a bit difficult for me to wrap my head around. Let me know if I need to explain into more detail.

[Leith Ross]

Hello Darkenergyz,

Maybe this will help. The code below concatenates the value of "X" into the formula.

Please Login or Register  to view this content.

[Darkenergyz]

I am not sure this will work, but i will try it out.

[Darkenergyz]

I just wanted to add a bit more detail for the objective I am trying to accomplish.

I have attached 2 screen shots, a "Before click" and "After Click".

I have also attached my test module.

I would like to keep the logic based on the current code that is attached.

Another thing to keep in mind; It would be prefable to continue adding criteria to the COUNTIFS statement based on the # of checkboxes selected.

[Darkenergyz]

Bump - Please help

[Darkenergyz]

Any ideas?

[Leith Ross]

Hello Darkenergyz,

I have made the changes to the attached workbook. The form now lets the user choose which value to filter by. The formula is also changed to reflect the filter count.

[Darkenergyz]

Very nice. Thanks!

The tricky part though; Is there a way to condition it on multiple checkboxes?

e.g., R1 and R2

Output: =COUNTIFS(C:C,2013,B:B,"R1B:B,"R2

Or for my case it would end up being <>R1 and <>R2 (Not R1/R2)

[Darkenergyz]

EDIT: Double-Post forum latency.


Very nice. Thanks!

The tricky part though; Is there a way to condition it on multiple checkboxes?

e.g., R1 and R2

Output: =COUNTIFS(C:C,2013,B:B,"R1",B:B,"R2")

Or for my case it would end up being <>R1 and <>R2 (Not R1/R2)

[Leith Ross]

Hello Hello Darkenergyz,

When using AutoFilter you can only filter using 1 or 2 criteria. To filter more columns you would need to use the AdvanceedFilter or some other method.

[Darkenergyz]

Hello Hello Darkenergyz,

When using AutoFilter you can only filter using 1 or 2 criteria. To filter more columns you would need to use the AdvanceedFilter or some other method.

Utilizing the code RudiS provided

Please Login or Register  to view this content.

I can filter multiple selections at once, but are you telling me it wouldn't be supported with the COUNTIFS statement? I only need to filter off of one collumn, it just might be several selections (Out of B:B).

Thank you so much for your help!

[Leith Ross]

Hello Darkenergyz,

The AutoFilter has to be applied to each column separately. When using AutoFilter, all affected columns are filtered simultaneously.

I really don't understand why you are using COUNTIFS, when this information can be obtained using VBA.

[Darkenergyz]

I hear you; This is just an example requirement. I take it you are referring to the count rows?

To give you a more in depth idea for what I am trying to do.

I have a SharePoint list that feeds an excel workbook through ODC.

the back end data is constantly changing per user data entry.

I require a dynamic solution that will show data in real time, which is why I am utilizing the formula. I am not using multiple columns, just one, with multiple fields within the column.

Sory for being a little confusing. I hope this helps

[Leith Ross]

Hello Darkenergyz,

Since you are using ODC with Sharepoint and you column headings are data compliant, I would probably use VBA and ADO to count and filter the data.

[Darkenergyz]

It's a bit more complicated then that. There are 4 share point databases linked into one master, built in sql/oracle. Unfortunately where this project stands with the multiple dimensions, I think a countifs is the most efficient way to continue to keep the integrity of the data without messing up anything.

The purpose of a COUNTIFS in my situation is to visually show #s and percentages based on a series of calculated values.

If there is a more efficient way to show this data possibly through VBA. (Using existing control panel).

Please elaborate. Thank you for taking the time to help tackle this.

V/r,

Josh

[Leith Ross]

Hello Josh,

Here is an example of what I was thinking about. I have added a ListBox to the UserForm. The ListBox is filled with rows from the worksheet based on the filter selections made using the check boxes.

The ADO connection is solely to the workbook. There should be no problems running this code in you current environment.

[Darkenergyz]

Hello Josh,

Here is an example of what I was thinking about. I have added a ListBox to the UserForm. The ListBox is filled with rows from the worksheet based on the filter selections made using the check boxes.

The ADO connection is solely to the workbook. There should be no problems running this code in you current environment.

Leith,

That is a great feature. I think I will definitely be using that in the near future.

The only issue is that it is a bit off course with current strategy.

This project has approximately 10 tables located in the backend with countifs formulas. The tables are then linked as pictures on approximately 10 worksheets with proprietary/unique data. The filters I am trying to set up will globally affect these est. 10 worksheets. (From one worksheet changing in the background.)

The COUNTIFS is going to be feeding data visualization in the front end. This is why I need this structure. I know it's not the best method, but this is the only way I know I can do to change the front end data within the front end worksheets in real time. I need to be able to visually present this information.

I have attached a simple data map to give you an idea of the structure. This isn't everything, but it should be enough to give you an idea. I know it can be done better, but I have limited resources. Again, thank you so much for the help.

Let me know if you need anything else.

Josh

[Leith Ross]

Hello Josh,

I like the graphic. What I am having trouble with is the "backend". How will it know what to count if a user has not input data for the filtering?

[Darkenergyz]

If a user hasn't selected anything (w/ Check box), it is going to count everything. The idea is to subtract when a checkbox is selected.

It will know with predefined COUNTIFS. The idea is to have the COUNTIFS statement change when checkboxes are selected from the control panel in the front end (See image Front End w/ Control Panel).

The criteria in the column is based on a drop down selection from SharePoint that is fed into the workbook.

When the user selects a checkbox from the control panel; based on the criteria it is going to change (like your radio buttons sheet) the COUNTIFS worksheet which feeds Worksheet 1-10 with data visualization.

I think I have an idea what to do based off of your radio button attachment. That workbook might be enough to get me where I need to be.

[Darkenergyz]

If we can back-track just a little;

based off of the ADO workbook, is there a way we can add a drop down box to filter, uhh lets say Year next to it?

So it would then show R1, R2, F4 based on criteria from a drop down box for year. (Keeping a select all too)

Lastly, is it possible to show a "counter" on the userform on the top right? Let's say, total row count AND total Yes in col D?

Please see attached screen shot. ADO.png

Thank you in advance.

EDIT: I think if we can do this, it will be absolute money!

Explanation: (Please see attached image CountIdea.png)
Col B - Count of filtered checkbox and a variable for the title header. e.g., Title for R1 in Year X
Col C - Variable for the title header in Year X e.g., Title for R1 in Year 2013 (w/ a condition that a dropdown box is added.
Col D - Transpose count under headers CatA, B and C. e.g., CatA 3, CatB, 1 CatC, 2

Let me know if this is possible.

V/r,

Josh

EDIT 2:This should allow me to create dynamic charts/graphs on one page that sync with the output.

[Darkenergyz]

bump - please see edit

[Leith Ross]

Hello Josh,

I can make those changes to the UserForm. Do I need to add the changes shown in the screen shot to the workbook also or do you have an updated workbook to post?

[Darkenergyz]

That would be awesome, but if you just post the logic, I should be able to work/produce off of that.

My sample workbook just has the formatted table on it, I have been multi-tasking with other parts of this.

I can attach if needed.

EDIT: For year I am also going to be forecasting. So with that in mind the logic will have to add.

For example:

2017

Current+2015+2016

I hope that makes sense.

EDIT: one more thing. Is there a way to exclude items instead of include?

[Leith Ross]

Hello Josh,

I made most of the changes to the UserForm. Are the CATA, CATB, etc. names of other columns on the worksheet? I'm asking because the workbook I have does not have any entries like these.

[Darkenergyz]

This is definetly on the right track. I will upload the sheet, but it’s really just sample data.

Do UserForms allow calculated fields?

Like total rows divided by type shown as a %.

Lastly can this data be published to a table on a spreadsheet?

(See Attached)