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

 

[Michael M]

Hey Kyle
Without checking all the data, both formula seem to be working OK on a limited test range.

No error values in the data ??

 

[kylefoley76]

the error value is N/A#

the only thing i can think of is that the difference between the functioning and the non functioning one is that in the non functioning one the values are derived from a vlookup formula, but even after i pasted values, i still had the same problem. here's what it looks like over on the time! sheet and trust me the dates in column c are correct because they work for the other formulas

 

[Michael M]

I didn't think the SumProduct would work with #N/A as part of the data !
Hmm, I note none of your formulae ranges accomodate column "M" ??

 

[kylefoley76]

ok, if i get rid of all the #n/a in column m then the formula will work but i really need those #n/a's in there because column m is calculated using vlookup and not every cell will be filled. how can i get around this problem

 

[Michael M]

Extend the formula,eg.

=IF(ISNA(vlookup(formula),"",Vlookup(formula)))

 

[kylefoley76]

i can't get the parentheses right

=IF(ISNA(vlookup(SUMPRODUCT(time!$M$2:time!$M$6416),--(time!$C$2:time!$C$6416=$A69))),""))

column c is not vlookup

 

[Michael M]

No !!
The formula goes where the Vlookup is used .....not in the Sumproduct !!
So, where the Vlookup formula gets an #N/A result you need to to get rid of the N/A by using the extended formula I supplied.

 

[Aladin Akyurek]

You have just 1 condition for totaling. In such cases, a SumIf formula
is the best thing to do:

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

If one of the ranges contains an error value like #N/A, say the C-range...

Control+shift+enter, not just enter:

=SUM(IF(1-ISNA(time!$C$2:$C$6416),IF(time!$C$2:$C$6416=$A69,
    time!$J$2:$J$6416)))

If both ranges contain an error value like #N/A...

Control+shift+enter, not just enter:

=SUM(IF(ISNUMBER((time!$C$2:$C$6416,$A69)*(time!$J$2:$J$6416)),
    IF(time!$C$2:$C$6416=$A69,time!$J$2:$J$6416)))

Note. A formula with SumProduct doesn't provide a solution that often.

 

[kylefoley76]

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))))