IF function SUM values problem

**pleiadeez7** · 10-13-2011, 10:53 AM

This should be an easy thing to do, but I'm really struggling this morning.

The forumlas in columns P, R and T are pulling data from a list that begins in column X. In an effort to clean up the data, I added a tab named Table Info and made some named ranges to pull from.

What I don't know how to do is pull together all of the information I need into one formula. For instance (in layman's terms), in column P:

If the value in A;A is in the "major_acct_numbers" range, see if L#/F#>0.2. If the result is L#>0 then sum the values of G#+H#+I#+J#. If the value of A:A is NOT in the range leave the cell empty.

Please help!

Janet

**NBVC** · 10-13-2011, 11:03 AM

Try:

=IF(A5="","",IF(ISNUMBER(MATCH(A5,Major_Acct_Numbers,0)),IF(AND(L5/F5>0.2,L5>0),G5+H5+I5+J5,0),""))

although all are returning blanks in the sample workbook.

**davegugg** · 10-13-2011, 11:04 AM

You can use a vlookup to see if the table has your value:

=IF(ISERROR(VLOOKUP(A6,'Table Data'!$A$2:$D$26,1,0)),"",IF(('AR DOM'!L6/'AR DOM'!F6)>0.2,'AR DOM'!G6+'AR DOM'!H6+'AR DOM'!I6+'AR DOM'!J6,""))

You may need to alter this formula as I'm not clear on the >0.2 or >0 part of your description.

**pleiadeez7** · 10-13-2011, 01:00 PM

Thanks NVBC! That worked like a charm. Now I need to know how to do the same thing in reverse. How do I write the formula to provide the results for all of the A;A values that are NOT in the "major_acct_number" range?

**NBVC** · 10-13-2011, 01:09 PM

Replace ISNUMBER in the formula with ISNA

**pleiadeez7** · 10-13-2011, 03:17 PM

As usual, that worked like a charm. If I can trouble you for one more thing, I SWEAR I will leave you alone! I have convoluted the following formula to such a degree that it gives a "FALSE" result instead of a numeric result. What am i doing wrong?!? Sighhh, I have a headache!

=IF(A384="","",IF(ISNUMBER(MATCH(A384,Dell_HP,0)),IF(AND(F384/$V$2<0.5,0,F384-$V$2*0.5-J384-K384-L384),IF(OR(ISNA(MATCH(A384,Dell_HP,0)),AND(N384="Special",F384/$V$2>0.2,F384-$V$2*0.2-K384-L384,F384-$V$2*0.2-J384-K384-L384),0),""))))

**NBVC** · 10-13-2011, 03:28 PM

Kind of hard to follow the whole logic, but I took a guess

=IF(A384="","",IF(ISNUMBER(MATCH(A384,Dell_HP,0)),IF(F384/$V$2<0.5,0,F384-$V$2*0.5-J384-K384-L384),IF(AND(N384="Special",F384/$V$2>0.2),F384-$V$2*0.2-K384-L384,F384-$V$2*0.2-J384-K384-L384)))

Please double check.

**pleiadeez7** · 10-13-2011, 04:10 PM

OHHH...there's supposed to be some form of logic involved! I don't have any of that! LOL

The stab you took got it half way there. I'm trying to do two things at once. In layman's terms what I'm trying to say is:

if A:A matches a value in the range "Dell_HP" then perform this calculation: (F384/$V$2<0.5,0,F384-$V$2*0.5-J384-K384-L384),

If A:A does NOT match the "Dell_HP" range BUT had the word "Special" in N:N then perform this calculation: F384/$V$2>0.2,F384-$V$2*0.2-K384-L384,F384-$V$2*0.2-J384-K384-L384

I'm so sorry to bother you with this. I feel like such a moron!

**NBVC** · 10-13-2011, 04:15 PM

Should there be an IF in front of (F384/$V$2<0.5,0,F384-$V$2*0.5-J384-K384-L384) and F384/$V$2>0.2,F384-$V$2*0.2-K384-L384,F384-$V$2*0.2-J384-K384-L384?

If so, then I guess a slight variation in my formula so that you get a 0 if N does not have "Special" in it...

=IF(A384="","",IF(ISNUMBER(MATCH(A384,Dell_HP,0)),IF(F384/$V$2<0.5,0,F384-$V$2*0.5-J384-K384-L384),IF(N384="Special",IF(F384/$V$2>0.2,F384-$V$2*0.2-K384-L384,F384-$V$2*0.2-J384-K384-L384),0)))