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:
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.
- 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 😛
Do you want to be awesome in Excel?
Here is a smart way to become awesome in Excel. Just signup for my Excel newsletter. Every week you will receive an Excel tip, tutorial, template or example delivered to your inbox. What more, as a joining bonus, I am giving away a 25 page eBook containing 95 Excel tips & tricks. Please sign-up below:
Your email address is safe with us. Our policies
More awesome tips for you:
Leave a Reply
|Use burn down Charts in your project management reports [bonus post]||We cant Cure Cancer, But we can Cure this Medicare Chart [Chart Busters]|