Complicated Formula

[Fgbdrum]

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.

 

[Sean Timmons]

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

 

[Eduardo]

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

 

[Fgbdrum]

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]

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

 

[Sean Timmons]

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))