Search code examples
excelexcel-formulaarray-formulas

SUMPRODUCT, CRITERIA and Blanks

I would like to multiply each fund monthly returns * monthly assets in the following snippet of data 

 FUND   Jan_returns   Feb_returns  Jan_Assets   Feb_Assets
   1        -2            3            200         300
   1         2            7            250         500   
   1         5            2            3000        
   2         6            5            500         600   
   2                      8            900          
   2         9                         1500        1500
   3        -6            3            100         1000 
   3        -7            4            660         520

For example, FUND 1 Jan_returns * Jan_assets = 15100

The currently formula is:

=SUMPRODUCT(($B$1:$B$8)($B$1:$B$10=A2),($D$1:$D$8)($B$1:$B$8=A2))

Where A2 is a reference to the FUND.

This is working for January. However, When I do this for February I am getting #Value! for all 3 funds. I think it is because of the blanks and tried <>"" but just got strange numbers.

The results should be 

   FUND   Jan      Feb
    1    15100     4400
    2    16500     3000
    3    -5220     5080

Any help in solving this problem is appreciated?

Solution

  • Like this, modify ranges as needed. Note that repeating the $B$1:$B$8=A2 is redundant* — you only need one instance.

    =SUMPRODUCT(($A$2:$A$9=$G2)*B$2:B$9*D$2:D$9)

    enter image description here

    *I'm assuming the 10 in $B$1:$B$10=A2 is a typo and should be 8.