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

Thanks,

>Can sumproduct be used to return cell content?

Numbers only, no text.

--
Biff
Microsoft Excel MVP


"Richard" <(E-Mail Removed)> wrote in message
news:6B573341-FDEF-494F-9A6E-(E-Mail Removed)...
> 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

On Feb 18, 5:02*pm, Richard <Rich...@discussions.microsoft.com> wrote:
> Can sumproduct be used to return cell content? *If so, does anybody have a
> sample that will requirte two conditions to be met.
>
> Thanks,

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

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

--

Dave Peterson

Dave,

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

Thanks,

"Dave Peterson" wrote:

> 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
>
> Richard wrote:
> >
> > Can sumproduct be used to return cell content? If so, does anybody have a
> > sample that will requirte two conditions to be met.
> >
> > Thanks,
>
> --
>
> Dave Peterson
>

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.


Richard wrote:
>
> Dave,
>
> I appreciate the info, but this is not working for me. I get a #VALUE
> error. Any suggestions?
>
> Thanks,
>
> "Dave Peterson" wrote:
>
> > 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
> >
> > Richard wrote:
> > >
> > > Can sumproduct be used to return cell content? If so, does anybody have a
> > > sample that will requirte two conditions to be met.
> > >
> > > Thanks,
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

I did array enter it. Do I have to use the entire column?

"Dave Peterson" wrote:

> 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.
>
>
> Richard wrote:
> >
> > Dave,
> >
> > I appreciate the info, but this is not working for me. I get a #VALUE
> > error. Any suggestions?
> >
> > Thanks,
> >
> > "Dave Peterson" wrote:
> >
> > > 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
> > >
> > > Richard wrote:
> > > >
> > > > Can sumproduct be used to return cell content? If so, does anybody have a
> > > > sample that will requirte two conditions to be met.
> > > >
> > > > Thanks,
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

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.



Richard wrote:
>
> I did array enter it. Do I have to use the entire column?
>
> "Dave Peterson" wrote:
>
> > 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.
> >
> >
> > Richard wrote:
> > >
> > > Dave,
> > >
> > > I appreciate the info, but this is not working for me. I get a #VALUE
> > > error. Any suggestions?
> > >
> > > Thanks,
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > 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
> > > >
> > > > Richard wrote:
> > > > >
> > > > > Can sumproduct be used to return cell content? If so, does anybody have a
> > > > > sample that will requirte two conditions to be met.
> > > > >
> > > > > Thanks,
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

It works! I didn't use the entire column, that was the issue.

Thank you very much!

"Dave Peterson" wrote:

> 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.
>
>
>
> Richard wrote:
> >
> > I did array enter it. Do I have to use the entire column?
> >
> > "Dave Peterson" wrote:
> >
> > > 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.
> > >
> > >
> > > Richard wrote:
> > > >
> > > > Dave,
> > > >
> > > > I appreciate the info, but this is not working for me. I get a #VALUE
> > > > error. Any suggestions?
> > > >
> > > > Thanks,
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > 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
> > > > >
> > > > > Richard wrote:
> > > > > >
> > > > > > Can sumproduct be used to return cell content? If so, does anybody have a
> > > > > > sample that will requirte two conditions to be met.
> > > > > >
> > > > > > Thanks,
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>