How to use SUMPRODUCT with INDEX MATCH multiple criteria
Requirement:
The user is trying to sum up the Net amount column if fund=Ctm0, AND IF TRADE TYPE =BUY. BELOW IS EXAMPLE OF the spreadsheet. Column starts with A1 and goes to I10
| Fund | Trade Type | Security Name | Settle Loc | Trade Date | Settle Date | Executing Broker | Share Quantity | Net Amount | |
| CTM0 | BUY | MSFT | US | 05/28/2013 | 06/04/2013 | DEUTSCHE BANK OOO | 9,358.00 | (78,929.31) | |
| CTM0 | BUY | MSFT | US | 05/28/2013 | 06/04/2013 | DEUTSCHE BANK OOO | 3,000.00 | (50,000.00) | |
| CTM0 | BUY | MSFT | US | 05/28/2013 | 06/04/2013 | DEUTSCHE BANK OOO | 4,000.00 | (60,000.00) | |
| CTM6 | BUY | MSFT | US | 05/28/2013 | 06/04/2013 | DEUTSCHE BANK OOO | 5,000.00 | (70,000.00) | |
| CTM6 | BUY | MSFT | US | 05/28/2013 | 06/04/2013 | DEUTSCHE BANK OOO | 6,000.00 | (90,000.00) | |
| CTM6 | SELL | MSFT | US | 05/28/2013 | 06/04/2013 | DEUTSCHE BANK OOO | 55,000.00 | 80,000.00 | |
| CTM9 | BUY | MSFT | US | 05/28/2013 | 06/04/2013 | DEUTSCHE BANK OOO | 3,000.00 | (50,000.00) | |
| CTM9 | BUY | MSFT | US | 05/28/2013 | 06/04/2013 | DEUTSCHE BANK OOO | 4,000.00 | (60,000.00) | |
| CTM9 | BUY | MSFT | US | 05/28/2013 | 06/04/2013 | DEUTSCHE BANK OOO | 5,000.00 | (70,000.00) |
Solution:
=SUMPRODUCT(($A$2:$A$10="CTM0")*($B$2:$B$10="BUY")*$I$2:$I$10)
Obtained from the OzGrid Help Forum.
Solution provided by GCExcel.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to set up a pop up message when sum of columns exceeds X |
| How to merge duplicate rows and sum value |
| How to use SUMIFS and include 'All' to Drop-down IndexMatch |
| How to sum cell numerical values based on text suffix |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
