# 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?

## 2 Answers

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