Results 1 to 5 of 5
Thread: Combination of IF and AND

020204, 11:07 #1Registered User
 Join Date
 Feb 2004
 Posts
 3
Unanswered: Combination of IF and AND
Hi
I am trying to have excel sum certain columns if another column = 1 and the sum of those columns is over a certain amount. I keep getting FALSE for the answer, so it must be missing the next argument. Any ideas?
=IF(AND(C3="1"),IF(H3>500,SUM(D3+E3+F3500),IF(H3>250,SUM(D3+E3+F3250)," ")))

020204, 11:23 #2Registered User
 Join Date
 Oct 2003
 Posts
 1,091
A couple of things:
Take out the " around 1, since it is a number (only text requires the quotes).
Also, your AND function does not include the second condition. You have too many IF's
Can you elaborate a little more on what you are trying to accomplish?
[EDIT:
Is this what you want?
=IF(AND((C3=1)*(H3>500)),D3+E3+F3500,IF(AND((C3=1)*(H3>250)*(H3<500)),D3+E3+F3250,""))
BTW, the space between the last set of quotes should be deleted if you want nothing to show.
On second edit, I think this will achieve what you want.
[/EDIT]Last edited by shades; 020204 at 11:37.
old, slow, and confused
but at least I'm inconsistent!
Rich
(retired Excel 2003 user, 3/28/2008)
How to ask a question on forums

020204, 11:52 #3Registered User
 Join Date
 Feb 2004
 Posts
 3
Thanks for the help. I really appreciate it.
Basically, I want Excel to put in the differenct between the sum of D3+E3+F3 and 500 if the sum of D3+E3+F3 is greater than 500 and the value of C3 = 1. If C3 <> 1 I want excel to put in the difference between the sum of D3+E3+F3 and 250 if the sum of D3+E3+F3 is greater than 250. SO it needs to check two things, 1) what C is equal to, and 2) what is the value of D3+E3+F3 and is it greater than X.
Originally posted by shades
A couple of things:
Take out the " around 1, since it is a number (only text requires the quotes).
Also, your AND function does not include the second condition. You have too many IF's
Can you elaborate a little more on what you are trying to accomplish?
[EDIT:
Is this what you want?
=IF(AND((C3=1)*(H3>500)),D3+E3+F3500,IF(AND((C3=1)*(H3>250)*(H3<500)),D3+E3+F3250,""))
BTW, the space between the last set of quotes should be deleted if you want nothing to show.
On second edit, I think this will achieve what you want.
[/EDIT]

020204, 12:06 #4Registered User
 Join Date
 Oct 2003
 Posts
 1,091
Try this:
=IF(AND((C3=1)*(D3+E3+F3)>500),D3+E3+F3500,IF(AND(C3<>1)*((D3+E3+F3)>250),D3+E3+F3250,""))old, slow, and confused
but at least I'm inconsistent!
Rich
(retired Excel 2003 user, 3/28/2008)
How to ask a question on forums

020204, 14:18 #5Registered User
 Join Date
 Feb 2004
 Posts
 3
Thanks!
Thanks so much! I was going in circles...
Originally posted by shades
Try this:
=IF(AND((C3=1)*(D3+E3+F3)>500),D3+E3+F3500,IF(AND(C3<>1)*((D3+E3+F3)>250),D3+E3+F3250,""))