OzGrid

How to use SUMPRODUCT with INDEX MATCH multiple criteria

< Back to Search results

 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: 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 use VBA to SUM result in last row in columns A:A

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



stars (0 Reviews)