Vlookup inside a Sumproduct

Question

I am trying to use a SUMPRODUCT function to essentially SUMIF. I need to use SUMPRODUCT because when I close the external workbook SUMIFs return errors. I am trying to sumif based on month, year, and height. All the data is in various workbooks. All observations, the month, and year data is in Workbook1. The height data is in workbook 2. I am using person name to lookup the height in the other workbook. The part of the formula looking at month and year work so I do not need that corrected. I believe there is something wrong with my vlookup. I'm not sure if I'm able to use vlookup in a sumproduct like this. Here is my function:

=sumproduct(('(Month Range'='Month)'*'(year range=year)'*(vlookup('personnamerange, height range, 2, false)))

Any advice would be greatly appreciated.

Answer 1

For this example, the relevant ranges are all located within the same sheet. You can adjust the ranges accordingly. Also, you'll need to make sure that the "height range" is sorted by person, and in ascending order.

L2:

=SUMPRODUCT(--($A$2:$A$10=J2),--($B$2:$B$10=K2),--(ISNUMBER(MATCH($C$2:$C$10,$F$2:$F$4,0))),LOOKUP($C$2:$C$10,$F$2:$F$4,$G$2:$G$4))

Note, however, if it can be guaranteed that each person in Column C occurs in Column F, the following formula can be used instead...

=SUMPRODUCT(--($A$2:$A$10=J2),--($B$2:$B$10=K2),LOOKUP($C$2:$C$10,$F$2:$F$4,$G$2:$G$4))

Hope this helps!