How to use SUMIFS to summarize the whole "sum_range", not only the rows where there is a criteria match?

[BGIskane]

I have a SUMIFS-formula which here gets its criteria-input from two drop down boxes (in my actual formula 10+ boxes). The table has three columns, the "Sum_range" contains sales and then I have the two Criteria_ranges called "Region" and "SalesResponsible". The values in the drop down boxes are for Region "West" and "East". For SalesResponsible I have "Joe" and "Anne". The SUMIFS works well as long as any of these criteria is selected. What I need help with is to also get the totals. I therefore included a third value in each of the two drop down boxes called "All regions" and "All sales reps". When I select any of these two values the SUMIFS of course doesn't work since there are no matches in the "criteria_range" colums. Therefore I wonder how I can get the formula to calculate, e.g. the grand total (consolidated sales of Anne and Joe in all regions or maybe the consolidated sales of "All sales reps" in region "West". I know that using "<>All regions" and "<>All sales reps" for the drop down boxes will get me close to what I am looking for but "<>" doesn't look very good in a drop down box so would be nice if there is another way. Any help would be appreciated.

 

[steve the fish]

You could do an IF test for the criteria such as:

IF(D1="All regions","<>",D1)

with in this example D1 housing your dropdown.

 

[BGIskane]

Thanks Steve. You gave me some inspiration so with a little bit of tweaking of your suggetion I now managed to get it to work...