Can Sumproduct return Text values

  • Thread starter Thread starter Richard
  • Start date Start date
R

Richard

Can sumproduct be used to return cell content? If so, does anybody have a
sample that will requirte two conditions to be met.

Thanks,
 
Give some details of your data, and what you are trying to do with it.
You can probably use an INDEX/MATCH combination, as SP returns
numbers.

Pete
 
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, you can use:

=sumproduct(--(othersheet!a1:a10=ctree),
--(othersheet!b1:b10=cleaf),
(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
 
Dave,

I appreciate the info, but this is not working for me. I get a #VALUE
error. Any suggestions?

Thanks,
 
Did you array enter it?

Did you use the full column?

Are you sure you have matches in all your fields?

It's worked for me lots of times.
 
Nope--you can only use the full column in xl2007.

Check your ranges. Are they all the same size?

How about the other questions?

I think it's time to share the formula you tried.


I did array enter it. Do I have to use the entire column?
Click to expand...
 
Back
Top