sumproduct

[kylefoley76]

i can't figure out why this formula isn't working

=SUMPRODUCT(time!$N$2:time!$N$6416,--(time!$C$2:time!$C$6416=$A69))

on the time! sheet I've got numbers in column N, the dates in time! column C match A69. so why isn't it working.

over in another column i've got the exact same formula with only small changes

=SUMPRODUCT(time!$J$2:time!$J$6416,--(time!$C$2:time!$C$6416=$A69))

and that formula is working fine. very strange

 

[Aladin Akyurek]

i'm still having problems with this. i hope someone can help.
i'm trying to make the formula so that the #n/a does not appear

=if(isna(VLOOKUP(L1912,food!$A$2:$D$28,4,0),"",vlookup(l1912,food!$a$2:$d$28,4,0))))

Try not to ignore the help that is given...

If the formula with VLOOKUP is expected to return a number, invoke:

On Excel 2007 and later...

=IFERROR(VLOOKUP(L1912,food!$A$2:$D$28,4,0),0)

On previous versions, if a numeric result is expected...

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,VLOOKUP(L1912,food!$A$2:$D$28,4,0)))

On the previous versions, if a text result is expected...

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",VLOOKUP(L1912,food!$A$2:$D$28,4,0)))

After suppressing #N/A by means of one of the foregoing formulas, which are less expensive, invoke a SUMIF formula (not a SumPRODUCT formula) as I suggested earlier.

 

[kylefoley76]

ok, thanks that did it.

 

[Michael M]

Close

=IF(ISNA(VLOOKUP(L1912,food!$A$2:$D$28,4,0)),"",VLOOKUP(L1912,food!$A$2:$D$28,4,0))