From: Amethyst on 28 Apr 2010 17:38 I'm using =SUMPRODUCT(--('sheetname'!\$c\$2:\$c\$100="SAname"),--('sheetname'!\$h\$2:\$h\$100)) to find an SA in Col C and add up his roll call in Col H. The formula worked in the original worksheet, but when additional lines were added to 'sheetname', the formula is returning #VALUE!. The formula looks OK. What went wrong? -- Amethyst From: Jim Thomlinson on 28 Apr 2010 17:54 Try this... =SUMPRODUCT(('sheetname'!\$c\$2:\$c\$100="SAname")*('sheetname'!\$h\$2:\$h\$100)) -- HTH... Jim Thomlinson "Amethyst" wrote: > I'm using > =SUMPRODUCT(--('sheetname'!\$c\$2:\$c\$100="SAname"),--('sheetname'!\$h\$2:\$h\$100)) > to find an SA in Col C and add up his roll call in Col H. The formula worked > in the original worksheet, but when additional lines were added to > 'sheetname', the formula is returning #VALUE!. The formula looks OK. What > went wrong? > -- > Amethyst From: Joe User on 28 Apr 2010 19:31 "Amethyst" wrote:> I'm using > =SUMPRODUCT(--('sheetname'!\$c\$2:\$c\$100="SAname"), > --('sheetname'!\$h\$2:\$h\$100)) [....] > the formula is returning #VALUE!. Try: =SUMPRODUCT(--('sheetname'!\$c\$2:\$c\$100="SAname"), 'sheetname'!\$h\$2:\$h\$100) The root cause of the problem is probably non-numeric values in H2:H100, e.g. null strings (""). Most non-numeric values [1] cause #VALUE errors in arithmetic expression such as --('sheetname'!\$h\$2:\$h\$100) and ('sheetname'!\$c\$2:\$c\$100="SAname")*('sheetname'!\$h\$2:\$h\$100)). But SUMPRODUCT treats non-numeric values in array arguments as zero. PS: Alternatively, you can use: =SUMIF('sheetname'!\$c\$2:\$c\$100, "SAname", 'sheetname'!\$h\$2:\$h\$100) ----- Endnotes: [1] Text that look likes a number is treated as a number in arithmetic expressions. Presumably that does not apply in this case since, then, you would not see a #VALUE error. ----- original message ----- "Amethyst" wrote: > I'm using > =SUMPRODUCT(--('sheetname'!\$c\$2:\$c\$100="SAname"),--('sheetname'!\$h\$2:\$h\$100)) > to find an SA in Col C and add up his roll call in Col H. The formula worked > in the original worksheet, but when additional lines were added to > 'sheetname', the formula is returning #VALUE!. The formula looks OK. What > went wrong? > -- > Amethyst