# Thread: IF Statement - refer to cells in a column Thanks: 0 Likes: 0

1. ## IF Statement - refer to cells in a column

Hi there,

I am stumped as to how to construct this part of my IF formula

Column
F25 = 333,000.....G25 = 100,000
F26 = 100,000.....G26 = 333,000
F27 = 20,000.......G27 = 0
F28 ....................G28
F29 ....................G29
F30 ....................G30

This is my attempt but it's returning #VALUE !

=IF(AND(G25:G30=0,F25:F30>0),TRUE,FALSE)

I would like it to look if cell G25 = 0 and F25 > 0, or G26 =0 and F26 >0, or G27 = 0 and F27 >0, etc

If any return True, then True, otherwise False. So above G27 and F27 return True, so overall formula return true.

I would like to refer to the column rather than individual cells, as I have a large range to look at.

Hopefully that makes some sense!!

Thanks   Reply With Quote

2. ## Re: IF Statement - refer to cells in a column

Try this formula in any cell
- it counts the number of times both conditions are met and returns true if one or more pairs satisfy the condition

=COUNTIFS(F:F,">0",G:G,0)>0  Reply With Quote

3. ## Re: IF Statement - refer to cells in a column

Try using OR instead of AND and confirm the formula with CTRL+SHIFT+ENTER.

Or try COUNTIFS:

=COUNTIFS(G25:G30,0, F25:G30,">0")>0  Reply With Quote

4. ## Re: IF Statement - refer to cells in a column

Oops, slight typo in the formula I posted. Should be this.

=COUNTIFS(G25:G30,0, F25:F30,">0")>0  Reply With Quote

5. ## Re: IF Statement - refer to cells in a column

Norie, thanks! That does work!! That gives me a great starting point to piece this particular formula together, which now should be possible.

I don't suppose you could give me your thoughts on my second thread "IF Statement - need to reduce size drastically"?  Reply With Quote

6. ## Re: IF Statement - refer to cells in a column Originally Posted by Norie Oops, slight typo in the formula I posted. Should be this.

=COUNTIFS(G25:G30,0, F25:F30,">0")>0
Could you please tell me what the >0 at the end of the formula is for?  Reply With Quote

7. ## Re: IF Statement - refer to cells in a column

Try the formula with and without >0 at the end and answer your own question   Reply With Quote

8. ## Re: IF Statement - refer to cells in a column

Thanks! Makes sense now..

I've incorporated this into my new formula but it's not giving the result I was expecting;

=IF(AND(COUNTIFS(G24:G33,0, F24:F33,">0")=0,AND(SUM(F24:G33)<>0),AND(COUNTIF(I24:I33,">25%"))),"No new transactions or variances +25%",)

The first part = 0, the second part does not = 0, and the third part, there are no percentages over 25%. So it should return "No new transactions or variances +25%" but it's returning 0.

Can you possibly see where I am going wrong?  Reply With Quote

## User Tag List

#### Tags for this Thread

g27, if statement, return, true, vba & excel 2010 #### Posting Permissions

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