How to return cell value based on multiple criteria in Excel?
If you have a range data shown as below, and you just want to quickly find the sales of AA in west region in 2-Jan, meanwhile, the salesmen must be J, how can you do? Of course, you can check them one by one, but here I can tell you a formula to quickly find out the cell value based on such multiple criteria in Excel.
There is an array formula that can help you return cell value based on multiple criteria.
Select a blank cell and type this formula =SUMPRODUCT((A2:A7=A2)*(B2:B7=B2)*(C2:C7=C6)*(D2:D7=D2)*(E2:E7)) ( A2:A7, B2:B7, C2:C7 and D2:D7 are the column ranges which the criteria is in; and A2, B2, C6 and D2 are the cells including criteria; E2:E7 is the column range where you want to find out the value meeting all criteria), and press Shift + Ctrl + Enter keys together, and then it will return the cell value.
Note: If there are more than one values meeting the criteria, the formula will sum up the values.
Best Office Productivity Tools
Supercharge Your Spreadsheets： Experience Efficiency Like Never Before with Kutools for Excel
Kutools for Excel boasts over 300 features, ensuring that what you need is just a click away...
Supports Office/Excel 2007-2021 & newer, including 365 | Available in 44 languages | Enjoy a full-featured 30-day free trial.
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!