=SUMPRODUCT question

[pickytweety]

=SUMPRODUCT((Name=$A$3)*(Test=$C10)*(Timing=D$6)*(Score))

What if "Score" above is a letter of the alphabet, not a number? I'm not
really wanting a sum, so much as a multiple criteria lookup. I used the
above formulas in a file for a teacher to create reports, but she's now
expanding that file and wants to include not just a child's score (80%) but
their reading level (aa1, b1, etc.). The score formula returns N/A's so we
switched to vlookups, but that required multiple ranges that are kind of a
hassle. It would be nice if the SUMPRODUCT above could just handle letters.
Any ideas?

 

[Dave Peterson]

Maybe...

=index(score,match(1,(Name=$A$3)*(Test=$C10)*(Timing=D$6),0))
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

I'm guessing that Score, Name, Test, timing are all single column ranges with
the same number of cells. And they are less than an entire column (if you're
using xl2007, the entire column is ok).

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))

============
If there is only one match and you're bringing back a number (or 0 if there is
no match for all the criteria), you can use:

=sumproduct(--(othersheet!a1:a10=a1),
--(othersheet!b1:b10=b1),
(othersheet!c1:c10))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html