Countifs multiple criteria dropdown list

[hasmani]

Hi all,

My first time ever posting a question on a forum, but after using google for 2 days to find an answer I have given up on google. And after searching for best excel forum, MrExcel.com came out on top. I hope you can help me with your knowledge.

I am trying to use countifs to sort data. I have made a dropdown selection list based on three topics: location /function and segment. I want to be able to select between different locations, functions and segments to sort the data but also be able to only select one topic to sort the data.

Locations: No selection, Amsterdam, Paris, Madrid and London.
Function: No selection, Banker and Assistant.
Segment: No selection, Master and Explore.

If I use the countifs function: =countifs(source[location],$A$1,source[function],$A$2,source[segment],$A$3) I am able to sort the data on three criteria. But I want to be able to also sort the data on 1 or 2 out of 3 criteria. So I want to be able to select "No selection" on location, "Banker" on function and "Master" on segment to see all the data for Bankers in the Master segment for all the locations. Or select "No selection" for location /function and "Master" for segment to see all the data for Master for Bankers and Assistants in all the locations.

 

[Eric W]

Welcome to the forum.

If instead of saying "No selection" in the drop-down, you use a "*", then you could use the formula as-is. If you want to leave it as "No selection", you could do something like:

=COUNTIFS(Source[location],IF($A$1="No selection","*",$A$1),Source[function],IF($A$2="No selection","*",$A$2),Source[segment],IF($A$3="No selection","*",$A$3))

I'm not sure what you mean about sorting the data though, this formula only counts certain rows.

 

[hasmani]

Hi Eric,

Thank you for your response. The solutions you gave are not working properly for example if I set all the topics on "No Selection" It should count 0 but it counts 24. Also it counts 24 Bankers but there are 27 Bankers in total.

I tried both the solutions you suggested.

I'm not sure what you mean about sorting the data though, this formula only counts certain rows.

I'm sorry you're right. I mean count.

 

[Eric W]

So are you saying that if one of the dropdowns says No selection, you want it to count nothing? As long as none of the items in your data columns say "No selection", then your original formula should work fine. You'd need to make an actual selection on all 3 dropdowns to get something to count.

Perhaps if you could show a small sample and the expected results, it would help.

 

[hasmani]

Yes so out of 3 dropdowns, if one says "No Selection" the formula should count that dropdown as 0 and show the counts of the other two dropdowns. For example if I want to see all counts for the bankers in all of the locations I want to select "No Selection" on location and segment but select "Banker" on function. Countifs formula should then give me the count for all Bankers in all the locations. If I select "No Selection" on location, "Banker" on function and "Master" on segment, it should show me all the Bankers with Master segment.

https://www.dropbox.com/s/61abia4zi44ucld/example.xlsx?dl=0

Above a link to an example file I made. In the middle you will see the selection options and on the right it should give me a view and the count.