sumproduct error

**krunk** · 05-30-2013, 05:30 PM

Hi, I really hope someone can help me solve this problem because it is driving me mad. I got it to work in a different workbook but now its not summing properly.

My formula is as follows: =IFERROR(VLOOKUP($B20,$J$11:$O$45,HLOOKUP(CONCATENATE($K$6,$D$10),$L$1:$O$2,2,FALSE),FALSE),SUMPRODUCT(($B$11:$B$36="Other")*($L$11:$L$36))+SUMPRODUCT(($B$11:$B$36="")*($L$11:$L$36)))

it is giving me a result of $39,680 but it should be giving me $40,073. I am attaching an example.

Thanks in advance for your help.

**XOR LX** · 05-30-2013, 05:48 PM

Hi,

The SUMPRODUCT in that cell is summing all values in L11:L36 for which the corresponding entry in B11:B36 is either blank or "Other". However, since cell B29 contains "GRAND TOTAL", the quantity in L29 (393.12) will be excluded from this summation, hence the discrepancy.

Regards

**FDibbins** · 05-30-2013, 06:00 PM

the 2nd part of your fomula (the sumproduct part) is based on data in B, but adding values in L. You have text 1/2 way down (Grand total) that matches neither criteria (other or ""), so the value on that row is being excluded.

On a side note, instead of sumproduct, you could use the easier sumif() =SUMIF($B$11:$B$36,"Other",$L$11:$L$36)+SUMIF($B$11:$B$36,"",$L$11:$L$36), and you could also replace concatenate
HLOOKUP(CONCATENATE("2012"&D$10),
with
HLOOKUP(K6&D$10,$L$1:$O$2,

**krunk** · 05-31-2013, 02:13 PM

I tried the IFERROR(VLOOKUP($B20,$J$11:$O$45,HLOOKUP(CONCATENATE($K$6,$D$10),$L$1:$O$2,2,FALSE),FALSE),SUMIF($B$11:$B$36,"Other",$L$11:$L$36)+SUMIF($B$11:$B$36,"",$L$11:$L$36)) but i still got the same answer. what am i doing wrong?

Thanks,