# How to sumif with multiple criteria

My sheet looks like this:

``````1|A      | B
2|amount | source
3|173.00 | bank1
4|173.00 | cc
5|173.00 | cash
6|173.00 | bank2
7|173.00 | cc
8|SUM
``````

This is simplified but what I want to do is Sumif A3:A7 if B3:B7 does not contain cash or cc. Sumif needs to either include or exclude 2 criterion.

My working solution is

``````=(sumif(B3:B7,"<>cc",A3:A7)-sumif(B3:B7,"cash",A3:A7))
``````

Just wanted to know if anyone knew a way to exclude/include more than one criteria in a straight-forward way?

Try this:

``````=ArrayFormula(sumproduct(\$A\$3:\$A\$7,\$B\$3:\$B\$7<>"cc",\$B\$3:\$B\$7<>"cash"))
``````

This is a way for you to apply two different evaluations of cell contents (cc and cash) to the same range of numbers.

• Yep! Work perfectly. Thanks! – gooddadmike Nov 29 '12 at 20:07
• This is such a powerful formula. Opens up a whole new world of possibilities... Helped me solve a problem that took me about 1 hour. Thanks! :) – Leniel Maccaferri Jan 10 '13 at 20:48

This is also a possible solution for this specific question:

``````=(sumif(B3:B7,"bank*",A3:A7))
``````

but the first answer is more flexible.

• Best to use the simplest solution when possible! Spreadsheet formulas can get out of hand really quickly, especially array formulas. – andrewb Oct 25 '14 at 1:20