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 Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
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!