I have a gradebook that returns values using the "LOOKUP"
function for a 60 question multiple choice test. I need
to sum the product of the returned values. I can use the
paste as text, then convert the text to a numeric value,
but I have to perform this function for each cell, and I
have 14,000 cells.

Is there a way to setup this formula to return a value
that i can then sum? =LOOKUP(I60,{"a","b","c","d","e"},
{"0","1","0","0","0"} The formula returns a 0 or 1 for
each test question but when I try and sum the results all
I get is a 0.
Thanks,
Bill

=SUMPRODUCT(LOOKUP(Range,{"a","b","c","d","e"},{0, 1,0,0,0})

Bill Harney wrote:
I have a gradebook that returns values using the "LOOKUP"
function for a 60 question multiple choice test. I need
to sum the product of the returned values. I can use the
paste as text, then convert the text to a numeric value,
but I have to perform this function for each cell, and I
have 14,000 cells.

Is there a way to setup this formula to return a value
that i can then sum? =LOOKUP(I60,{"a","b","c","d","e"},
{"0","1","0","0","0"} The formula returns a 0 or 1 for
each test question but when I try and sum the results all
I get is a 0.
Thanks,
Bill

Correcting for a missing paren...

=SUMPRODUCT(LOOKUP(Range,{"a","b","c","d","e"},{0, 1,0,0,0}))

Aladin Akyurek wrote:
=SUMPRODUCT(LOOKUP(Range,{"a","b","c","d","e"},{0, 1,0,0,0})

Bill Harney wrote:

I have a gradebook that returns values using the "LOOKUP" function for
a 60 question multiple choice test. I need to sum the product of the
returned values. I can use the paste as text, then convert the text to
a numeric value, but I have to perform this function for each cell,
and I have 14,000 cells.
Is there a way to setup this formula to return a value that i can then
sum? =LOOKUP(I60,{"a","b","c","d","e"},
{"0","1","0","0","0"} The formula returns a 0 or 1 for each test
question but when I try and sum the results all I get is a 0.
Thanks,
Bill