Match Lookup question

[Guest]

Hey everyone, I am not sure what the best way is to handle this so I thought
that maybe some of you could help point me in the right direction. In my
worksheet (Sheet1) I have the following data:

Sheet1:

a b c d e
f
1 Promo1 Promo1 Promo2 Promo2 Promo2
2 Week1 Week2 Week3 Week4 Week5
3 Menu Item1 .2 .3 .15 .26 .16
4 Menu Item2 .4 .28 .56 .6
..68
5 Menu Item3 .25 .32 .45 .8 .15

And just to specifiy there are many more promo's and many more menu items in
my sheet so this is an oversimplification of the data.

That being said, I would like to sum the data for a specific Menu Item based
on which promo it falls under. For example:

Sheet2:

a b c
1 Promo2 Menu Item2 1.84


Any help on the best way to do this would be greatly appreciated.

Thanks in advance for all of your help.

Beset,
Chad

 

[Bob Phillips]

=SUMPRODUCT((Sheet1!B1:M1=A1)*(Sheet1!A3:A20=Sheet2!B1)*(Sheet1!B3:M20))

adjust the ranges to suit

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Bob Phillips]

=SUMPRODUCT((Sheet1!B1:M1=A1)*(Sheet1!A3:A20=Sheet2!B1)*(Sheet1!B3:M20))

adjust the ranges to suit

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Ragdyer]

I can't figure out why the unary fails!

Might you explain Bob?

 

[Ragdyer]

I can't figure out why the unary fails!

Might you explain Bob?

 

[Aladin Akyurek]

C1 on Sheet2:

=SUMIF($B$1:$F$1,A1,INDEX($B$3:$F$5,MATCH(B1,$A$3:$A$5,0),0))

 

[Aladin Akyurek]

C1 on Sheet2:

=SUMIF($B$1:$F$1,A1,INDEX($B$3:$F$5,MATCH(B1,$A$3:$A$5,0),0))

 

[Aladin Akyurek]

I can't figure out why the unary fails!

Might you explain Bob?

[...]

SumProduct with the comma-syntax requires equally sized objects. Put
otherwise:

SumProduct(Vector1,Vector2,...)
SumProduct(Matrix1,Matrix2,...)

but not:

SumProduct(Vector1,Vector2,...,Matrix1,...)

If you switch to using the multiplication operator ("the star syntax"),
it behaves like MMult.

BTW, the OP's question doesn't require SumProduct at all.

direction. In
my

 

[Aladin Akyurek]

I can't figure out why the unary fails!

Might you explain Bob?

[...]

SumProduct with the comma-syntax requires equally sized objects. Put
otherwise:

SumProduct(Vector1,Vector2,...)
SumProduct(Matrix1,Matrix2,...)

but not:

SumProduct(Vector1,Vector2,...,Matrix1,...)

If you switch to using the multiplication operator ("the star syntax"),
it behaves like MMult.

BTW, the OP's question doesn't require SumProduct at all.

direction. In
my

 

[RagDyer]

Being an AVID proponent of using the asterisk form of SumProduct, mainly
because the "regular" asterisk syntax in these groups always exposes
(coerces) the numerical range to a mathematical operator, I would *never*
have attempted a unary solution to this post.

In fact, I answered a similar post a couple of days ago with almost the
exact same type solution:

http://tinyurl.com/gt8f3

Where the ranges varied.

The only reason I gave this thread a second thought was because Bob was the
author of the suggestion, and Bob doesn't use the asterisk form.
I just wondered why he did use it here, and I soon found out when I tried
the unary in his formula.

Thanks for the explanation.

Just another reason why I feel the asterisk form should be the primary
syntax of choice.<bg>

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

I can't figure out why the unary fails!

Might you explain Bob?

[...]

SumProduct with the comma-syntax requires equally sized objects. Put
otherwise:

SumProduct(Vector1,Vector2,...)
SumProduct(Matrix1,Matrix2,...)

but not:

SumProduct(Vector1,Vector2,...,Matrix1,...)

If you switch to using the multiplication operator ("the star syntax"), it
behaves like MMult.

BTW, the OP's question doesn't require SumProduct at all.

direction. In
my

 

[RagDyer]

Being an AVID proponent of using the asterisk form of SumProduct, mainly
because the "regular" asterisk syntax in these groups always exposes
(coerces) the numerical range to a mathematical operator, I would *never*
have attempted a unary solution to this post.

In fact, I answered a similar post a couple of days ago with almost the
exact same type solution:

http://tinyurl.com/gt8f3

Where the ranges varied.

The only reason I gave this thread a second thought was because Bob was the
author of the suggestion, and Bob doesn't use the asterisk form.
I just wondered why he did use it here, and I soon found out when I tried
the unary in his formula.

Thanks for the explanation.

Just another reason why I feel the asterisk form should be the primary
syntax of choice.<bg>

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

I can't figure out why the unary fails!

Might you explain Bob?

[...]

SumProduct with the comma-syntax requires equally sized objects. Put
otherwise:

SumProduct(Vector1,Vector2,...)
SumProduct(Matrix1,Matrix2,...)

but not:

SumProduct(Vector1,Vector2,...,Matrix1,...)

If you switch to using the multiplication operator ("the star syntax"), it
behaves like MMult.

BTW, the OP's question doesn't require SumProduct at all.

direction. In
my

 

[Bob Phillips]

If you recall my paper Rick, I say ... There is no situation that I know of
whereby a solution using -- could not be achieved somehow with a '*'.
Conversely, ...

This is one of those occasions.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

Being an AVID proponent of using the asterisk form of SumProduct, mainly
because the "regular" asterisk syntax in these groups always exposes
(coerces) the numerical range to a mathematical operator, I would *never*
have attempted a unary solution to this post.

In fact, I answered a similar post a couple of days ago with almost the
exact same type solution:

http://tinyurl.com/gt8f3

Where the ranges varied.

The only reason I gave this thread a second thought was because Bob was the
author of the suggestion, and Bob doesn't use the asterisk form.
I just wondered why he did use it here, and I soon found out when I tried
the unary in his formula.

Thanks for the explanation.

Just another reason why I feel the asterisk form should be the primary
syntax of choice.<bg>

--
Regards,

RD

-------------------------------------------------------------------------- -
Please keep all correspondence within the NewsGroup, so all may benefit !
-------------------------------------------------------------------------- -

I can't figure out why the unary fails!

Might you explain Bob?

[...]

SumProduct with the comma-syntax requires equally sized objects. Put
otherwise:

SumProduct(Vector1,Vector2,...)
SumProduct(Matrix1,Matrix2,...)

but not:

SumProduct(Vector1,Vector2,...,Matrix1,...)

If you switch to using the multiplication operator ("the star syntax"), it
behaves like MMult.

BTW, the OP's question doesn't require SumProduct at all.

=SUMPRODUCT((Sheet1!B1:M1=A1)*(Sheet1!A3:A20=Sheet2!B1)*(Sheet1!B3:M20))

adjust the ranges to suit

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

Hey everyone, I am not sure what the best way is to handle this so
I
thought
that maybe some of you could help point me in the right

direction. In
my

worksheet (Sheet1) I have the following data:

Sheet1:

a b c d e
f
1 Promo1 Promo1 Promo2 Promo2 Promo2
2 Week1 Week2 Week3 Week4 Week5
3 Menu Item1 .2 .3 .15 .26
.16
4 Menu Item2 .4 .28 .56 .6
.68
5 Menu Item3 .25 .32 .45 .8
.15

And just to specifiy there are many more promo's and many more

menu
items

in
my sheet so this is an oversimplification of the data.

That being said, I would like to sum the data for a specific Menu Item
based
on which promo it falls under. For example:

Sheet2:

a b c
1 Promo2 Menu Item2 1.84


Any help on the best way to do this would be greatly appreciated.

Thanks in advance for all of your help.

Beset,
Chad

 

[Bob Phillips]

If you recall my paper Rick, I say ... There is no situation that I know of
whereby a solution using -- could not be achieved somehow with a '*'.
Conversely, ...

This is one of those occasions.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

Being an AVID proponent of using the asterisk form of SumProduct, mainly
because the "regular" asterisk syntax in these groups always exposes
(coerces) the numerical range to a mathematical operator, I would *never*
have attempted a unary solution to this post.

In fact, I answered a similar post a couple of days ago with almost the
exact same type solution:

http://tinyurl.com/gt8f3

Where the ranges varied.

The only reason I gave this thread a second thought was because Bob was the
author of the suggestion, and Bob doesn't use the asterisk form.
I just wondered why he did use it here, and I soon found out when I tried
the unary in his formula.

Thanks for the explanation.

Just another reason why I feel the asterisk form should be the primary
syntax of choice.<bg>

--
Regards,

RD

-------------------------------------------------------------------------- -
Please keep all correspondence within the NewsGroup, so all may benefit !
-------------------------------------------------------------------------- -

I can't figure out why the unary fails!

Might you explain Bob?

[...]

SumProduct with the comma-syntax requires equally sized objects. Put
otherwise:

SumProduct(Vector1,Vector2,...)
SumProduct(Matrix1,Matrix2,...)

but not:

SumProduct(Vector1,Vector2,...,Matrix1,...)

If you switch to using the multiplication operator ("the star syntax"), it
behaves like MMult.

BTW, the OP's question doesn't require SumProduct at all.

=SUMPRODUCT((Sheet1!B1:M1=A1)*(Sheet1!A3:A20=Sheet2!B1)*(Sheet1!B3:M20))

adjust the ranges to suit

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

Hey everyone, I am not sure what the best way is to handle this so
I
thought
that maybe some of you could help point me in the right

direction. In
my

worksheet (Sheet1) I have the following data:

Sheet1:

a b c d e
f
1 Promo1 Promo1 Promo2 Promo2 Promo2
2 Week1 Week2 Week3 Week4 Week5
3 Menu Item1 .2 .3 .15 .26
.16
4 Menu Item2 .4 .28 .56 .6
.68
5 Menu Item3 .25 .32 .45 .8
.15

And just to specifiy there are many more promo's and many more

menu
items

in
my sheet so this is an oversimplification of the data.

That being said, I would like to sum the data for a specific Menu Item
based
on which promo it falls under. For example:

Sheet2:

a b c
1 Promo2 Menu Item2 1.84


Any help on the best way to do this would be greatly appreciated.

Thanks in advance for all of your help.

Beset,
Chad

 

[Aladin Akyurek]

If you recall my paper Rick, I say ... There is no situation that I know of
whereby a solution using -- could not be achieved somehow with a '*'.

Then you need to edit your "paper" for when you apply Sumproduct to a
range (to be conditionally summed) consisting of formulas returning
blanks (i.e., ""), the star idiom will simply flounder. It doesn't help
much objecting that such a range should not house any such blanks.

Conversely, ...

This is one of those occasions.

See my reply which forwards a formula with SumIf, not one with
SumProduct of any kind.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

Being an AVID proponent of using the asterisk form of SumProduct, mainly
because the "regular" asterisk syntax in these groups always exposes
(coerces) the numerical range to a mathematical operator, I would *never*
have attempted a unary solution to this post.

In fact, I answered a similar post a couple of days ago with almost the
exact same type solution:

http://tinyurl.com/gt8f3

Where the ranges varied.

The only reason I gave this thread a second thought was because Bob was the
author of the suggestion, and Bob doesn't use the asterisk form.
I just wondered why he did use it here, and I soon found out when I tried
the unary in his formula.

Thanks for the explanation.

Just another reason why I feel the asterisk form should be the primary
syntax of choice.<bg>

--
Regards,

RD

-------------------------------------------------------------------------- -
Please keep all correspondence within the NewsGroup, so all may benefit !
-------------------------------------------------------------------------- -

Ragdyer wrote:
I can't figure out why the unary fails!

Might you explain Bob?
[...]

SumProduct with the comma-syntax requires equally sized objects. Put
otherwise:

SumProduct(Vector1,Vector2,...)
SumProduct(Matrix1,Matrix2,...)

but not:

SumProduct(Vector1,Vector2,...,Matrix1,...)

If you switch to using the multiplication operator ("the star syntax"), it
behaves like MMult.

BTW, the OP's question doesn't require SumProduct at all.

=SUMPRODUCT((Sheet1!B1:M1=A1)*(Sheet1!A3:A20=Sheet2!B1)*(Sheet1!B3:M20))
adjust the ranges to suit

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

Hey everyone, I am not sure what the best way is to handle this so
I
thought
that maybe some of you could help point me in the right
direction. In
my
worksheet (Sheet1) I have the following data:

Sheet1:

a b c d
e
f
1 Promo1 Promo1 Promo2 Promo2
Promo2
2 Week1 Week2 Week3 Week4
Week5
3 Menu Item1 .2 .3 .15 .26
.16
4 Menu Item2 .4 .28 .56 .6
.68
5 Menu Item3 .25 .32 .45 .8
.15
And just to specifiy there are many more promo's and many more menu
items
in
my sheet so this is an oversimplification of the data.

That being said, I would like to sum the data for a specific
Menu Item
based
on which promo it falls under. For example:

Sheet2:

a b c
1 Promo2 Menu Item2 1.84


Any help on the best way to do this would be greatly appreciated.

Thanks in advance for all of your help.

Beset,
Chad

 

[Aladin Akyurek]

If you recall my paper Rick, I say ... There is no situation that I know of
whereby a solution using -- could not be achieved somehow with a '*'.

Then you need to edit your "paper" for when you apply Sumproduct to a
range (to be conditionally summed) consisting of formulas returning
blanks (i.e., ""), the star idiom will simply flounder. It doesn't help
much objecting that such a range should not house any such blanks.

Conversely, ...

This is one of those occasions.

See my reply which forwards a formula with SumIf, not one with
SumProduct of any kind.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

Being an AVID proponent of using the asterisk form of SumProduct, mainly
because the "regular" asterisk syntax in these groups always exposes
(coerces) the numerical range to a mathematical operator, I would *never*
have attempted a unary solution to this post.

In fact, I answered a similar post a couple of days ago with almost the
exact same type solution:

http://tinyurl.com/gt8f3

Where the ranges varied.

The only reason I gave this thread a second thought was because Bob was the
author of the suggestion, and Bob doesn't use the asterisk form.
I just wondered why he did use it here, and I soon found out when I tried
the unary in his formula.

Thanks for the explanation.

Just another reason why I feel the asterisk form should be the primary
syntax of choice.<bg>

--
Regards,

RD

-------------------------------------------------------------------------- -
Please keep all correspondence within the NewsGroup, so all may benefit !
-------------------------------------------------------------------------- -

Ragdyer wrote:
I can't figure out why the unary fails!

Might you explain Bob?
[...]

SumProduct with the comma-syntax requires equally sized objects. Put
otherwise:

SumProduct(Vector1,Vector2,...)
SumProduct(Matrix1,Matrix2,...)

but not:

SumProduct(Vector1,Vector2,...,Matrix1,...)

If you switch to using the multiplication operator ("the star syntax"), it
behaves like MMult.

BTW, the OP's question doesn't require SumProduct at all.

=SUMPRODUCT((Sheet1!B1:M1=A1)*(Sheet1!A3:A20=Sheet2!B1)*(Sheet1!B3:M20))
adjust the ranges to suit

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

Hey everyone, I am not sure what the best way is to handle this so
I
thought
that maybe some of you could help point me in the right
direction. In
my
worksheet (Sheet1) I have the following data:

Sheet1:

a b c d
e
f
1 Promo1 Promo1 Promo2 Promo2
Promo2
2 Week1 Week2 Week3 Week4
Week5
3 Menu Item1 .2 .3 .15 .26
.16
4 Menu Item2 .4 .28 .56 .6
.68
5 Menu Item3 .25 .32 .45 .8
.15
And just to specifiy there are many more promo's and many more menu
items
in
my sheet so this is an oversimplification of the data.

That being said, I would like to sum the data for a specific
Menu Item
based
on which promo it falls under. For example:

Sheet2:

a b c
1 Promo2 Menu Item2 1.84


Any help on the best way to do this would be greatly appreciated.

Thanks in advance for all of your help.

Beset,
Chad

 

[RagDyer]

If it's your development, and it's your choice as to what formulas to use,
and you know data can just as well be imported, as well as keyed in, where
there is *definitely a chance* of mixed value types, where you might need
some checks and balances, then you simply conditionally sum using zeroes
instead of zero length strings!

In such a situation (mixed data possibilities), I cannot see any reason for
by-passing any additional means of validation of data.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

If you recall my paper Rick, I say ... There is no situation that I know
of
whereby a solution using -- could not be achieved somehow with a '*'.

Then you need to edit your "paper" for when you apply Sumproduct to a
range (to be conditionally summed) consisting of formulas returning blanks
(i.e., ""), the star idiom will simply flounder. It doesn't help much
objecting that such a range should not house any such blanks.

Conversely, ...

This is one of those occasions.

See my reply which forwards a formula with SumIf, not one with SumProduct
of any kind.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

Being an AVID proponent of using the asterisk form of SumProduct, mainly
because the "regular" asterisk syntax in these groups always exposes
(coerces) the numerical range to a mathematical operator, I would
*never*
have attempted a unary solution to this post.

In fact, I answered a similar post a couple of days ago with almost the
exact same type solution:

http://tinyurl.com/gt8f3

Where the ranges varied.

The only reason I gave this thread a second thought was because Bob was the
author of the suggestion, and Bob doesn't use the asterisk form.
I just wondered why he did use it here, and I soon found out when I
tried
the unary in his formula.

Thanks for the explanation.

Just another reason why I feel the asterisk form should be the primary
syntax of choice.<bg>

--
Regards,

RD

-------------------------------------------------------------------------- -
Please keep all correspondence within the NewsGroup, so all may benefit
!
-------------------------------------------------------------------------- -


Ragdyer wrote:
I can't figure out why the unary fails!

Might you explain Bob?
[...]

SumProduct with the comma-syntax requires equally sized objects. Put
otherwise:

SumProduct(Vector1,Vector2,...)
SumProduct(Matrix1,Matrix2,...)

but not:

SumProduct(Vector1,Vector2,...,Matrix1,...)

If you switch to using the multiplication operator ("the star syntax"), it
behaves like MMult.

BTW, the OP's question doesn't require SumProduct at all.

=SUMPRODUCT((Sheet1!B1:M1=A1)*(Sheet1!A3:A20=Sheet2!B1)*(Sheet1!B3:M20))
adjust the ranges to suit

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

Hey everyone, I am not sure what the best way is to handle this so
I
thought
that maybe some of you could help point me in the right
direction. In
my
worksheet (Sheet1) I have the following data:

Sheet1:

a b c d
e
f
1 Promo1 Promo1 Promo2 Promo2
Promo2
2 Week1 Week2 Week3 Week4
Week5
3 Menu Item1 .2 .3 .15 .26
.16
4 Menu Item2 .4 .28 .56 .6
.68
5 Menu Item3 .25 .32 .45 .8
.15
And just to specifiy there are many more promo's and many more menu
items
in
my sheet so this is an oversimplification of the data.

That being said, I would like to sum the data for a specific
Menu Item
based
on which promo it falls under. For example:

Sheet2:

a b c
1 Promo2 Menu Item2 1.84


Any help on the best way to do this would be greatly appreciated.

Thanks in advance for all of your help.

Beset,
Chad

 

[RagDyer]

If it's your development, and it's your choice as to what formulas to use,
and you know data can just as well be imported, as well as keyed in, where
there is *definitely a chance* of mixed value types, where you might need
some checks and balances, then you simply conditionally sum using zeroes
instead of zero length strings!

In such a situation (mixed data possibilities), I cannot see any reason for
by-passing any additional means of validation of data.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

If you recall my paper Rick, I say ... There is no situation that I know
of
whereby a solution using -- could not be achieved somehow with a '*'.

Then you need to edit your "paper" for when you apply Sumproduct to a
range (to be conditionally summed) consisting of formulas returning blanks
(i.e., ""), the star idiom will simply flounder. It doesn't help much
objecting that such a range should not house any such blanks.

Conversely, ...

This is one of those occasions.

See my reply which forwards a formula with SumIf, not one with SumProduct
of any kind.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

Being an AVID proponent of using the asterisk form of SumProduct, mainly
because the "regular" asterisk syntax in these groups always exposes
(coerces) the numerical range to a mathematical operator, I would
*never*
have attempted a unary solution to this post.

In fact, I answered a similar post a couple of days ago with almost the
exact same type solution:

http://tinyurl.com/gt8f3

Where the ranges varied.

The only reason I gave this thread a second thought was because Bob was the
author of the suggestion, and Bob doesn't use the asterisk form.
I just wondered why he did use it here, and I soon found out when I
tried
the unary in his formula.

Thanks for the explanation.

Just another reason why I feel the asterisk form should be the primary
syntax of choice.<bg>

--
Regards,

RD

-------------------------------------------------------------------------- -
Please keep all correspondence within the NewsGroup, so all may benefit
!
-------------------------------------------------------------------------- -


Ragdyer wrote:
I can't figure out why the unary fails!

Might you explain Bob?
[...]

SumProduct with the comma-syntax requires equally sized objects. Put
otherwise:

SumProduct(Vector1,Vector2,...)
SumProduct(Matrix1,Matrix2,...)

but not:

SumProduct(Vector1,Vector2,...,Matrix1,...)

If you switch to using the multiplication operator ("the star syntax"), it
behaves like MMult.

BTW, the OP's question doesn't require SumProduct at all.

=SUMPRODUCT((Sheet1!B1:M1=A1)*(Sheet1!A3:A20=Sheet2!B1)*(Sheet1!B3:M20))
adjust the ranges to suit

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

Hey everyone, I am not sure what the best way is to handle this so
I
thought
that maybe some of you could help point me in the right
direction. In
my
worksheet (Sheet1) I have the following data:

Sheet1:

a b c d
e
f
1 Promo1 Promo1 Promo2 Promo2
Promo2
2 Week1 Week2 Week3 Week4
Week5
3 Menu Item1 .2 .3 .15 .26
.16
4 Menu Item2 .4 .28 .56 .6
.68
5 Menu Item3 .25 .32 .45 .8
.15
And just to specifiy there are many more promo's and many more menu
items
in
my sheet so this is an oversimplification of the data.

That being said, I would like to sum the data for a specific
Menu Item
based
on which promo it falls under. For example:

Sheet2:

a b c
1 Promo2 Menu Item2 1.84


Any help on the best way to do this would be greatly appreciated.

Thanks in advance for all of your help.

Beset,
Chad