OzGrid

How to use SUMPRODUCT with INDEX MATCH multiple criteria

Category: [Excel]  Demo Available

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:

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

stars (0 Reviews)