If then statements - 3 variables

[rraymond]

What does an if/then statement look like that requires three variables to be met before an action can occur?

 

[Ron Coderre]

There are a few approaches...depending on your needs.
Can you describe the criteria and what you want to happen when they are met...and not met?

 

[Jonmo1]

You can put AND as the criteria in IF


=IF(AND(a = b,c > d,e < f),Value if True, Value If False)<F),VALUE p if value true, false)<>

Hope that helps.

 

[rraymond]

This is the formula that I tried using, but it's not working:

=IF(AND('April 2011 Raw Data'!D:D="Northeast",'April 2011 Raw Data'!E:E="Acura",'April 2011 Raw Data'!F:F=1),IF(ISNA(MATCH(B2,'April 2011 Raw Data'!G:G)),0,INDEX('April 2011 Raw Data'!J:J,0)))

What I'm trying to do is get a numerical value, but the formula returned a FALSE. Huh? I need for the formula to match three variables in a row: Northeast, Acura, and a ranking (in this case 1), and then return a sales value.

In the formula above, the B2 cell reference is a dealer name, which will vary, which is why I need the formula to act upon a cell reference, not a set/unique identifier.

 

[njimack]

An IF function won't be able to evaluate multiple rows of data like that - you need SUMPRODUCT, which doesn't accept entire columns as ranges. Something like the below:

=SUMPRODUCT(--('April 2011 Raw Data'!D2:D100="Northeast"),--('April 2011 Raw Data'!E2:E100="Acura"),--('April 2011 Raw Data'!F2:F100=1),--(2011 Raw Data'!G2:G100=B2),('April 2011 Raw Data'!J2:J100))

 

[rraymond]

I tried that sumproduct formula and it didn't work, even after re-working the ranges. Do I copy the whole thing, even with dashes? I've never used a sumproduct formula before.

 

[njimack]

Yes - copy and paste the entire formula. You'll need to adjust the ranges if you have more than 100 rows of data.

 

[rraymond]

It's still not working. I can't understand this. I've tried permutations of half a dozen formulas. There has to be a solution here. I can get temporary solutions that work for this month, but which would still require manually data entry, altering dealer names for hundreds of rows each month.

 

[njimack]

What does the formula return?
Can you post a sample of your data?

 

[rraymond]

It returns a #NAME? error. How do I post a copy of my data so that it looks like a spreadsheet with rows/cells rather than just a mass of columns and text?