Not always we want to lookup values based on one search parameter. For eg. Imagine you have data like below and you want to find how much sales Joseph made in January 2007 in North region for product “Fast car”?
Simple, use your index finger to scan the list and find the match 😉
Of course, that wouldn’t be scalable. Plus, you may want to put your index finger to better use, like typing . So, lets come up with some formulas that do this for us.
You can extract items from a table that match multiple criteria in multiple ways. See the examples to understand the techniques:
|Using SUMIFS Formula [help]|
|Using SUMPRODUCT Formula [help]|
|Using INDEX & Match Formulas (Array Formula) [help]|
|Using VLOOKUP Formula [help]|
|Conditions:||A helper column that concatenates month, salesman, region & product in the left most column of tblData2|
|Using SUM (Array Formula) [help]|
Go ahead and download the file. It also has some homework for you to practice these formula tricks.
Also checkout the examples Vinod has prepared.
Special Thanks to
- Excel SUMIFS Formula – What is it, how to use it & examples
- Excel SUMPRODUCT Formula – What is it, how to use it & examples
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
|« How to Lookup Values to Left?||6 Tips for Writing Better VLOOKUPs »|