Using a oggle button criteria within a countifs function

[Slurry Pumper]

Hi all,

I am trying to utilize a countifs function that would search a range of cells, and would include information based on if a toggle button is false or true. So far I have the current countifs below for you to peruse at your leisure.

=COUNTIFS('The Data'!$B$2:$B$5000,'Overall Head to Head'!$A$2,'The Data'!$C$2:$C$5000,'Overall Head to Head'!$E$2,'The Data'!$O$2:$O$5000,"W",'The Data'!$A$2:$A$5000,">=" & 'Overall Head to Head'!$H$2,'The Data'!$A$2:$A$5000,"<=" & 'Overall Head to Head'!$L$2)

So basically the formula will include everything from the "The Data" sheet column B that is equivalent to whatever the value of cell A2 in the "Overall Head to Head" sheet is and...

everything from the "The Data" sheet column C that is equivalent to whatever the value of cell E2 in the "Overall Head to Head" sheet is and...

everything from the "The Data" sheet column O that is equal to "W" and...

everything from the "The Data" sheet column A that is within a range >= with the value on sheet "Overall Head to Head" cell H2 and <= the value on sheet "Overall Head to Head" cell L2.


So now I want to add a criteria that will take into account if a toggle button is true or false. Is there a way to do this, I've experimented with a few things, and while the formula accepted the text without errors, I could not get a satisfactory result.

Any examples would be appreciated.

Thanks.

[jason.b75]

You omitted the most critical information from your post.

The criteria for the formula when the toggle is true, and for when it is false.

[Slurry Pumper]

O.K.

So what I want to do is have a toggle button set up that represents a week, in my manipulation of the data, I need to look at separate weeks and include a few weeks together and omit other weeks on a seemingly random basis. I tried all kinds of tactics, below is the latest version when I tried to include a if statement. The formula was taken from a different cell, so some of the original criteria is changed slightly.


=COUNTIFS('The Data'!$B$2:$B$5000,'Overall Head to Head'!$A$2,'The Data'!$C$2:$C$5000,'Overall Head to Head'!$E$2,'The Data'!$I$2:$I$5000,"W",'The Data'!$A$2:$A$5000,">=" & 'Overall Head to Head'!$H$2,'The Data'!$A$2:$A$5000,"<=" & 'Overall Head to Head'!$L$2,'The Data'!$D$2:$D$5000,IF(ToggleButton1="On","1","0"),'The Data'!$D$2:$D$5000,IF(ToggleButton2="On","0","1"),'The Data'!$D$2:$D$5000,IF(ToggleButton3="On","0","2"),'The Data'!$D$2:$D$5000,IF(ToggleButton4="On","0","3"),'The Data'!$D$2:$D$5000,IF(ToggleButton5="On","0","4"))

So basically there are 5 separate weeks that are available to scrutiny, and if one particular week's toggle button is selected, I would like the countifs to count that week, and if it isn't then don't count it. In the formula above, I tried using true/false, or just using the togglebutton name, and several other experiments that all were accepted into the formula with no results. Experiments all range from manipulating the if statement.

[jason.b75]

Ok, it makes sense now.

You can only have one criteria per range when the requirement is to look for equal values, with the way you are trying to do it, the formula is looking for rows that are equal to 1 AND 2 AND 3, rather than 1 OR 2 OR 3. Does that makes sense?

There is a workaround though, is it possible to attach a copy of your file with any personal / confidential data removed? It's much easier if we have something to work with.

[Slurry Pumper]

I think I was able to do it. I guess I'll find out as soon as I submit reply.

So the countifs statements are located under the pie charts. This workbook is under construction so there are allot of things that will be removed and added later. The specific area I am looking at is in the "Overall Hd to Hd Specific Weeks" sheet, and the formula is located in cell A7.

I'm sure there are more efficient ways of doing what I'm doing, but this file represents my hacking at excel since 2005, so it is what it is.

[jason.b75]

Ok, not sure if it's because the formula is wrong, or because I know nothing about NFL

On the first chart I'm getting a result of 7 (100%) for week 3 and 0 for anything else, does that sound correct?

[Slurry Pumper]

The numbers are represented in the pivot table also with a green color on the "Opponent" column, but yes, the answer should be 7 for that particular cell. It represents the total number of times the New York Jets won (ATS) playing against the New York Giants during week 3 of the preseason.

I just noticed that your location is in England, so I guess I can forgive the lack of knowledge of the NFL. The same can be said for my knowledge of soccer. I just found out from our resident foreigner at work that there is a world cup thing coming in a week or so, so good luck for your England team.

[jason.b75]

Ok, see if this works for you, I'll attach the file with the changes made for you to compare, but it might be easier for you to understand if you follow it from the beginning.

First of all, you need to link the buttons to individual cells in the worksheet.

Go to the developer tab, select Design Mode, then Properties.

The dropdown should default to Sheet1 worksheet, change the dropdown to ToggleButton1.

About halfway down the list of property options you will see 'LinkedCell' Enter A2 into the empty box next to LinkedCell.

Change the dropdown to ToggleButton2, and enter B2, Button3, C2, etc. After the last button is done, close the properties window, and click 'Design Mode' again to return to normal.

Now for the formula

=SUM(COUNTIFS('The Data'!$B$2:$B$5000,'Overall Head to Head'!$A$2,'The Data'!$C$2:$C$5000,'Overall Head to Head'!$E$2,'The Data'!$I$2:$I$5000,"W",'The Data'!$A$2:$A$5000,">=" & 'Overall Head to Head'!$H$2,'The Data'!$A$2:$A$5000,"<=" & 'Overall Head to Head'!$L$2,'The Data'!$D$2:$D$5000,CHOOSE({1,2,3,4,5},IF(A2,0),IF(B2,1),IF(C2,2),IF(D2,3),IF(E2,4))))

If you look at the bold section, you will see that it occupies a single criteria within the countifs formula, the array that this employs carries out 5 counts at once, 1 for each week, then adds them together.

Note that this is an array formula, which has to be entered in a slightly different way, if you're not used to this.
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Hopefully you should be able to adapt that formula to the remainder of your charts.

I heard something about that world cup thingy too, while most people will be intently focused on grown men chasing a bag of air around a field, I will be staring out of the window counting raindrops

[Slurry Pumper]

Thanks, I'll give it a go and see what happens. The instructions are pretty clear however, so even I have a good chance to pull this off. I like the adjustable size of the toggle button to change chart information, and after the preseason is complete, I can work on the regular season which has more criteria to choose from. It looks like you have opened an entirely new look to my charts, and I suspect the final product will look very different from what it looks like now. Perhaps only 1 sheet with a dozen charts all controlled with a toggle button for criteria.

Thanks.

[Slurry Pumper]

Not for nothing, can the formula be changed to count the false portions of the toggle button instead of counting the true times buttons are selected. The toggle button has an inherently bad look for true and false modes. It is at a point where I would say that the false mode look like it should be the true mode and vice versa.

And how do I label this thread solved. I keep looking for a button in the forum somewhere but haven't located a way to label it solved just yet.

[jason.b75]

Reversing the logic of the formula is easy enough

IF(NOT(A2),0)

You would need to do that for each of the 5 IF's in the formula.

To mark your thread as solved, look to the right of the page just above your first post, there is a dropdown, 'Thread Tools' you should be able to do it from there.

I'l keep an eye on the thread in case you come back with any follow up issues.

[Slurry Pumper]

Thanks again, and my retirement account thanks you as well. Enjoy the rain.