Sumifs and Criteria - how to set “all” criteria?

1
votes

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.

2
excel
Don't have time to test, so I'll post it as a comment. Something like this should get you close: =IF(A1="All",SUMIFS(Amount,Type,"<>""",Amount,">0"),SUMIFS(Amount,Type,$A$1,Amount,">0")) - sous2817
@pnuts oh, I like yours better! - sous2817
@pnuts Sure, but given the information provided by the OP, my vote is for your formula. At the very least, both of our formulas should give the OP enough information to modify it to suit their data set. - sous2817
@pnuts - that's a good idea! I will try that out. - BruceWayne

2 Answers

2
votes

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"))
1
votes

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