IF Statement - refer to cells in a column

[tlc53]

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

 

[Yongle]

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

 

[Norie]

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

 

[Norie]

Oops, slight typo in the formula I posted.

Should be this.

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

 

[tlc53]

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"?

 

[tlc53]

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?

 

[Yongle]

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

 

[tlc53]

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?