Sumifs and Criteria - how to set "all" criteria?

I have a table that is like this - starting in cell B1:

Type    Amount
Bat     123
Bat     321
Bat     123
Bat     354
Car     154
Car     156
Car     15688
Car     154

I have a SUMIF that will look at the Type, and return the Sum. In "A1" I have a data validation with the choices "All", "Bat", and "Car". The "Type" column is a named range ("Type"), same with "Amount" column.

I have a formula =sumifs(Amount,Type,$A$1,Amount,">0"). This works great, EXCEPT when I want to sum all of them (choosing "All" in A1).

How can I get the formula to, if I have "All" chosen in A1, to sum Bat + Car? Does that make sense? In some cases I want to know Bat's sum, in others, Car's sum, and others I want the total total.

edit: The amount being >0 is just an example, that Sumifs has a bunch of criteria, I just kept it short for example purposes.

Solution 1:

You should check the value of A1 first with an IF statement and apply the appropriate formula. Something like:

=IF(A1="All",SUMIFS(Amount,Type,"<>""",Amount,">0"),SUMIFS(Amount,Type,$A$1,Amount,">0"))

or more better (courtesy of @pnuts):

=IF(A1="All",SUM(Amount),SUMIFS(Amount,Type,$A$1,Amount,">0"))

Solution 2:

Try this:

=IF($A$1="All", SUMIF(Amount,">0"), SUMIFS(Amount,Type,$A$1,Amount,">0"))

I got it working and then saw pnuts and sous2817's answer. All similar .. this one includes the additional >0 criteria you have as well