Data Validation List (Yes, No, ALL)

[tcustance]

Hi All,

I am using SUMIFS if the results in my list are met. I have either Y or N. I am wondering if I can add another drop down that would be ALL. I am looking to be able to filter our all the Y, N, or ALL if I want to sum all of the results.

My formula is as follows; the bold is the part where it references the data validation.

=SUMIFS(Queue!$D:$D,Queue!$C:$C,"*-06*",Queue!$H:$H,"="&Q7,Queue!$K:$K,">""",Queue!$G:$G,"="&$D$4,Queue!$F:$F,"="&Calendar!$D$3)

Thanks,
Trent

 

[DanteAmor]

I am trying to understand your form, since without data, I can only guess.
If in column H you have values ​​equal to Q7 then it is not necessary to put "="&Q7, only Q7 goes, the same for D4 and D3.
And for column K if it is greater than 0.

Then:

=SUMIFS(Queue!$D1:$D10,Queue!$C1:$C10,"*-06*",Queue!$H1:$H10,Q7,Queue!$K1:$K10,">0",Queue!$G1:$G10,D$4,Queue!$F1:$F10,$D$3)

In the end you need something like this:

=SUM(SUMIFS(Queue!$D1:$D10,Queue!$C1:$C10,"*-06*",Queue!$H1:$H10,Q7,Queue!$K1:$K10,">0",Queue!$G1:$G10,D$4,Queue!$F1:$F10,{"Y","N","ALL"}))

 

[Eric W]

Welcome to the forum.

Dante is correct when he says that you don't need the equal signs, but they don't hurt.

But if you are willing to use Y, N, * as your data validation list, using a * to indicate "ALL", you don't need to change your formula at all. The * acts as a wildcard and will match anything, Y or N.

 

[tcustance]

Welcome to the forum.

Dante is correct when he says that you don't need the equal signs, but they don't hurt.

But if you are willing to use Y, N, * as your data validation list, using a * to indicate "ALL", you don't need to change your formula at all. The * acts as a wildcard and will match anything, Y or N.

Awesome! Thank you both. I am wondering if there is a way have the text display "All", while using the *? If not, this gets me where I want it to be at least.

Thanks again
Trent

 

[DanteAmor]

Awesome! Thank you both. I am wondering if there is a way have the text display "All", while using the *? If not, this gets me where I want it to be at least.

Thanks again
Trent

In D3 you can put Y, N or All, the formula would change All to *

=SUMIFS(Queue!$D1:$D10,Queue!$C1:$C10,"*-06*",Queue!$H1:$H10,Q7,Queue!$K1:$K10,">0",Queue!$G1:$G10,D$4,Queue!$F1:$F10,LOOKUP(D3,{"All","N","Y"},{"*","N","Y"}))

 

[Eric W]

If you want to actually use the word "ALL" in your drop-down list, you'll need to change the formula somehow. There are several ways to do that. Dante's suggestion is as good as any of them.

 

[DanteAmor]

Another shorter option.


=SUMIFS(Queue!$D1:$D10,Queue!$C1:$C10,"*-06*",Queue!$H1:$H10,Q7,Queue!$K1:$K10,">0",Queue!$G1:$G10,D$4,Queue!$F1:$F10,IF(D3="All","*",D3))