Complicated Formula
 

Hello,

Here's what I am trying to accomplish:

Sheet 1
Column A Column B
Row 1 A AA
Row 2 B BB
Row 3 C CC

Sheet 2
Column H Column J Column M
Row 1 A AA 150
Row 2 B BB 200
Row 3 A CC 300

I want the forumla to say:

If cell A1 in Sheet 1 appears anywhere in column H in Sheet 2, AND cell B1
in Sheet 1 appears anywhere in column J in Sheet 2, then give me the value
for it from column M in Sheet 2. Such a formula would return a result of 150.

Any help would be most appreciated. Thank you.

Complicated Formula
 

=SUMPRODUCT(--(Sheet2!H1:H10000=A1),--(Sheet2!J1:J10000=B1),Sheet2!M1:M10000)

"Fgbdrum" wrote:

Hello,

Here's what I am trying to accomplish:

Sheet 1
Column A Column B
Row 1 A AA
Row 2 B BB
Row 3 C CC

Sheet 2
Column H Column J Column M
Row 1 A AA 150
Row 2 B BB 200
Row 3 A CC 300

I want the forumla to say:

If cell A1 in Sheet 1 appears anywhere in column H in Sheet 2, AND cell B1
in Sheet 1 appears anywhere in column J in Sheet 2, then give me the value
for it from column M in Sheet 2. Such a formula would return a result of 150.

Any help would be most appreciated. Thank you.

Complicated Formula
 

Hi,
try
=SUMPRODUCT(--(Sheet2!H1:H10000=A1),--(Sheet2!J1:J10000=B1),Sheet2!M1:M10000)



"Fgbdrum" wrote:

Hello,

Here's what I am trying to accomplish:

Sheet 1
Column A Column B
Row 1 A AA
Row 2 B BB
Row 3 C CC

Sheet 2
Column H Column J Column M
Row 1 A AA 150
Row 2 B BB 200
Row 3 A CC 300

I want the forumla to say:

If cell A1 in Sheet 1 appears anywhere in column H in Sheet 2, AND cell B1
in Sheet 1 appears anywhere in column J in Sheet 2, then give me the value
for it from column M in Sheet 2. Such a formula would return a result of 150.

Any help would be most appreciated. Thank you.

Complicated Formula
 

Hello,

Tried out the formula and here's what it looks like in real life:

=SUMPRODUCT(--(SalPlans!$A$2:$A$781='All
Other'!$I3682),--(SalPlans!$G$2:$G$781='All
Other'!$AQ3682),SalPlans!$H$2:$H$781)

For the most part, it is spot on, however, I am asking it to return a number
to me, for example, 30,000. In some spots, it is taking that number and
doubling it and returning 60,000 as the result. Any idea why that might be
happening? Thanks again.

"Eduardo" wrote:

Hi,
try
=SUMPRODUCT(--(Sheet2!H1:H10000=A1),--(Sheet2!J1:J10000=B1),Sheet2!M1:M10000)



"Fgbdrum" wrote:

Hello,

Here's what I am trying to accomplish:

Sheet 1
Column A Column B
Row 1 A AA
Row 2 B BB
Row 3 C CC

Sheet 2
Column H Column J Column M
Row 1 A AA 150
Row 2 B BB 200
Row 3 A CC 300

I want the forumla to say:

If cell A1 in Sheet 1 appears anywhere in column H in Sheet 2, AND cell B1
in Sheet 1 appears anywhere in column J in Sheet 2, then give me the value
for it from column M in Sheet 2. Such a formula would return a result of 150.

Any help would be most appreciated. Thank you.

Complicated Formula
 

Hi,
Check for duplications, your formula looks right, you have the same
information twice

"Fgbdrum" wrote:

Hello,

Tried out the formula and here's what it looks like in real life:

=SUMPRODUCT(--(SalPlans!$A$2:$A$781='All
Other'!$I3682),--(SalPlans!$G$2:$G$781='All
Other'!$AQ3682),SalPlans!$H$2:$H$781)

For the most part, it is spot on, however, I am asking it to return a number
to me, for example, 30,000. In some spots, it is taking that number and
doubling it and returning 60,000 as the result. Any idea why that might be
happening? Thanks again.

"Eduardo" wrote:

Hi,
try
=SUMPRODUCT(--(Sheet2!H1:H10000=A1),--(Sheet2!J1:J10000=B1),Sheet2!M1:M10000)



"Fgbdrum" wrote:

Hello,

Here's what I am trying to accomplish:

Sheet 1
Column A Column B
Row 1 A AA
Row 2 B BB
Row 3 C CC

Sheet 2
Column H Column J Column M
Row 1 A AA 150
Row 2 B BB 200
Row 3 A CC 300

I want the forumla to say:

If cell A1 in Sheet 1 appears anywhere in column H in Sheet 2, AND cell B1
in Sheet 1 appears anywhere in column J in Sheet 2, then give me the value
for it from column M in Sheet 2. Such a formula would return a result of 150.

Any help would be most appreciated. Thank you.

Complicated Formula
 

If you are ok with the same information being in there multiple times, just do

=SUMPRODUCT(--(SalPlans!$A$2:$A$781='All
Other'!$I3682),--(SalPlans!$G$2:$G$781='All
Other'!$AQ3682),SalPlans!$H$2:$H$781)/SUMPRODUCT(--(SalPlans!$A$2:$A$781='All
Other'!$I3682),--(SalPlans!$G$2:$G$781='All Other'!$AQ3682))

"Eduardo" wrote:

Hi,
Check for duplications, your formula looks right, you have the same
information twice

"Fgbdrum" wrote:

Hello,

Tried out the formula and here's what it looks like in real life:

=SUMPRODUCT(--(SalPlans!$A$2:$A$781='All
Other'!$I3682),--(SalPlans!$G$2:$G$781='All
Other'!$AQ3682),SalPlans!$H$2:$H$781)

For the most part, it is spot on, however, I am asking it to return a number
to me, for example, 30,000. In some spots, it is taking that number and
doubling it and returning 60,000 as the result. Any idea why that might be
happening? Thanks again.

"Eduardo" wrote:

Hi,
try
=SUMPRODUCT(--(Sheet2!H1:H10000=A1),--(Sheet2!J1:J10000=B1),Sheet2!M1:M10000)



"Fgbdrum" wrote:

Hello,

Here's what I am trying to accomplish:

Sheet 1
Column A Column B
Row 1 A AA
Row 2 B BB
Row 3 C CC

Sheet 2
Column H Column J Column M
Row 1 A AA 150
Row 2 B BB 200
Row 3 A CC 300

I want the forumla to say:

If cell A1 in Sheet 1 appears anywhere in column H in Sheet 2, AND cell B1
in Sheet 1 appears anywhere in column J in Sheet 2, then give me the value
for it from column M in Sheet 2. Such a formula would return a result of 150.

Any help would be most appreciated. Thank you.