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 😛
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
Thank you and see you around.
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] »|