IF Formula, NEED HELP!

[Guest]

I have two worksheets in one workbook. Both contain tables, one condenses
data from the other. I am needing to pull specific data from a column as
result of data in two other columns with the end result in the second
worksheet. Normally I would use the VLookup; however I have two ranges and
this is not possible. So far this is the formula I have come up with, yet I
have not be able to make it work.

=IF('Report Data'!F7:F65536=1),('Report Data'!R7:R65536=QL), I1:I65536

PLEASE HELP!

 

[Bob Phillips]

=INDEX(I1:I65535,MATCH(1,('Report Data'!F7:F65535=1)*('Report
Data'!R7:R65535="QL"),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

If the F7 = 1 and R7 = QL you want I1 to appear in the cell your formula is in?

=IF(AND(F7=1,R7="QL"),I1,"")

You would then copy the formula down if what I said in the 1st line is true

 

[Guest]

This didnt work. I am not sure how to expalin what I need. I am fairly new to
as advanced of work in excel as this. I also noticed that what I had earlier
was written wrong, but either way neither formulas worked. I adjusted the
formula you offered and it still gave the error.

=IF('Report Data'!F7:F65536=1),('Report Data'!R7:R65536=QL), SUM('Report
Data'!I1:I65536)

Breakdown:
IF('Report Data'!F7:F65536=1) - this is the first range of data needing to
be reviewed

('Report Data'!R7:R65536=QL) - this is the second range

SUM('Report Data'!I1:I65536) - This should be the outcome. Summing what
matches with the two listed ranges above.

 

[Guest]

This didnt work. I am not sure how to expalin what I need. I am fairly new to
as advanced of work in excel as this. I also noticed that what I had earlier
was written wrong, but either way neither formulas worked. I adjusted the
formula you offered and it still gave the error.

=IF('Report Data'!F7:F65536=1),('Report Data'!R7:R65536=QL), SUM('Report
Data'!I1:I65536)

Breakdown:
IF('Report Data'!F7:F65536=1) - this is the first range of data needing to
be reviewed

('Report Data'!R7:R65536=QL) - this is the second range

SUM('Report Data'!I1:I65536) - This should be the outcome. Summing what
matches with the two listed ranges above.

 

[Bob Phillips]

=SUMPRODUCT(--('Report Data'!F7:F65535=1),
--('Report Data'!R7:R65535="QL"),I1:I65535)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

I got a value error on this as well.

=SUMPRODUCT(--('Report Data'!F7:F65535=1),
--('Report Data'!R7:R65535="QL"),I1:I65535)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[RagDyeR]

All ranges *must* be the same size.

Start Column I range at row 7, to equal the other ranges!
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


I got a value error on this as well.

 

[Guest]

Thank you so much Bob and Rag. It worked PERFECT!!!!

 

[Guest]

I do have a couple quick questions.

1. How do I lock formulas so that they cannot be deleted without locking the
sheet?

2.In the final formula:
=SUMPRODUCT(--('Report Data'!$F$7:$F$65535=1),--('Report
Data'!$R$7:$R$65535="QL"),'Report Data'$I$7:$I$65535)

What is the significance of the dashes "--"?

 

[Ragdyer]

AFAIK, you'll need code to protect the formulas without protecting the
sheet.

As for the double unary (--), it changes the True and False returns from the
SumProduct formula to 1's and 0's, so that they can be used in the
calculations.

I personally prefer the asterisk form:

=SUMPRODUCT(('Report Data'!$F$7:$F$65535=1)*('Report
Data'!$R$7:$R$65535="QL")*'Report Data'!$I$7:$I$65535)

Since it will create an error message if any of the data is corrupted, and
not just calculate the "good" data, without letting you know that
'something' is remiss.

Anyway, for a more extensive explanation of the unary, try these links:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

http://www.mcgimpsey.com/excel/formulae/doubleneg.html

 

[Guest]

AFAIK? I have no clue what this is and will probably kick myself.
Thank you for all of your help and explanations.

 

[Bob Phillips]

As Far As I Know - http://www.acronymdictionary.co.uk/

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

HAHAHA!
Like I said, a nice kick in the butt.

Thank you both for all of your help!

 

[RagDyeR]

We appreciate the feed-back.
--

Regards,

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

HAHAHA!
Like I said, a nice kick in the butt.

Thank you both for all of your help!