Sumif with multiple conditions [quick tip]

Here is a little formula trick if you need to sum a range of cells based on multiple conditions.

Assuming you have the starfleet, captain and flight data like this (seriously, what are you doing with excel again?)

In order to findout how many flights James Kirk did on the Amarillo, you can write an array formula like this:
=SUM(($D$3:$D$24)*($B$3:$B$24="Amarillo")*($C$3:$C$24="James Kirk"))

Remember, the above is an array formula. So you need to type it and press CTRL+SHIFT+Enter.

Update: Or you can also use SUMPRODUCT (replace SUM in the above formula with SUMPRODUCT and just hit Enter (Thanks to Simon for teaching me this tip)

As you can guess, column B has ship names, C has captains and D has # of flights.

Using the same logic, you can make a cross tab (or pivot) like the one shown on the right too.

Further Resources:

• Excel quick tips – Fast and fun way to learn microsoft excel
• Excel array formulas – Tutorials and examples
• 8 ways to use SUMIF() and COUNTIF() formulas
• List of startfleet ship names 😛