Using a drop down list field to determine a criteria in Countifs function - need ALL

Hi there,

So I am currently populating a table by using the countifs function based on 4 separate criteria, one of which is using a drop down list field as the criteria. What I would like to do is hbe able to have an "All" option, so rather than just selecting one item from the drop down menu, I'd like to be able to select everything so that I can also get a total? Is this possible?

Thanks

Try

=COUNTIFS(range1,criteria1,range2,IF(criteria2="All","<>",criteria2))

Hi Jonmo,

Thanks for the quick response. However, I need the "all" option to be in the drop down list field, not the actual formula. The idea is that I can change the drop down to repopulate my results as necessary, but I would like to see the individual options, and a total or all option as well?

Is this possible?

Thanks

What's stopping you from putting the word ALL in the dropdown list?

Didn't mean to sound so blunt.

Here's an example
The word All is included in the DataValidation dropdown.

I only used countif (not countifS), but the principle is the same for either..

EFLucied.xlsx

I don't want the actual word ALL. ALL isn't part of the drop down list or the data. So for example... in my dropdown list I have: A, B, C, D. I want to be able to choose one of each, but then also see the results of the total of A, B, C & D together. Does that make more sense?

I'm suggesting you add the word All to the dropdown.
Sorry, I can't think of any other way to do it.

It doesn't have to be the word all, it can be anything you want, even a blank.
That might actually be best, to include all the criteria in the count, leave the criteria cell blank, with nothing selected from the dropdown and use

=COUNTIFS(range1,criteria1,range2,IF(criteria2="","<>",criteria2))

I worked it out! I added the wildcard value "*" to the list so that if that is chosen, it counts everything.

Thank you for all your help!

Glad you got resolution.

FYI, that will only work on TEXT criteria.
It will not include numeric values in the count.
Also if there are formula blanks (="") in the range, it WILL count those.