IF Statement - refer to cells in a column

tlc53

Active Member
Joined
Jul 26, 2018
Messages
365
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 :)
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,252
Office Version
365
Platform
Windows
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
 
Last edited:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,459
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,459
Office Version
365
Platform
Windows
Oops, slight typo in the formula I posted.:eek:

Should be this.

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

tlc53

Active Member
Joined
Jul 26, 2018
Messages
365
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

Active Member
Joined
Jul 26, 2018
Messages
365
Oops, slight typo in the formula I posted.:eek:

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

Well-known Member
Joined
Mar 11, 2015
Messages
5,252
Office Version
365
Platform
Windows
Try the formula with and without >0 at the end and answer your own question ;)
 

tlc53

Active Member
Joined
Jul 26, 2018
Messages
365
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?
 
Last edited:

Forum statistics

Threads
1,089,330
Messages
5,407,618
Members
403,155
Latest member
ValenBaez

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top