Complicated Formula

F

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.
 
E

Eduardo

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

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.
 
E

Eduardo

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top