# Thread: Combination of IF and AND

1. Registered 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+F3-500),IF(H3>250,SUM(D3+E3+F3-250)," ")))

2. Registered 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+F3-500,IF(AND((C3=1)*(H3>250)*(H3<500)),D3+E3+F3-250,""))

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; 02-02-04 at 12:37.

3. Registered 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+F3-500,IF(AND((C3=1)*(H3>250)*(H3<500)),D3+E3+F3-250,""))

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]

4. Registered User
Join Date
Oct 2003
Posts
1,091
Try this:

=IF(AND((C3=1)*(D3+E3+F3)>500),D3+E3+F3-500,IF(AND(C3<>1)*((D3+E3+F3)>250),D3+E3+F3-250,""))

5. Registered 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+F3-500,IF(AND(C3<>1)*((D3+E3+F3)>250),D3+E3+F3-250,""))

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•