Forum Discussion
How to nest IF statement with COUNTIF
Hi there,
I am currently using =COUNTIFS(FY2:FY50, "<=13",FY2:FY50, ">=12") but I realized that I don't want the function to run if a number in another cell is less than 13 (the number in another cell dictates whether or not I want to run this formula)
So I want to add onto this function a IF statement or something that tells the function not to run if the number in GJ13 is <13 but don't know how.
The following formula will leave the cell with the formula blank if GJ13 is less than 13.
=IF(GJ13<13, "", COUNTIFS(FY2:FY50, "<=13",FY2:FY50, ">=12"))
If you want it to return something else, change "". For example:
=IF(GJ13<13, "Not Applicable", COUNTIFS(FY2:FY50, "<=13",FY2:FY50, ">=12"))
or
=IF(GJ13<13, 0, COUNTIFS(FY2:FY50, "<=13",FY2:FY50, ">=12"))
- Hi Hans,
would something like this work as well? =IF(AND(GJ4>3),COUNTIFS(FY2:FY50, "<=4",FY2:FY50, ">=3"), #N/A)
It seems to be working but I wanted to check in case it could create errors in the futureYou mentioned GJ13 <13 earlier. The opposite of that would be GJ13>=13, not GJ13>3.
But it's up to you to decide which is correct.
There is no need to use AND here. AND(GJ4>3) is equivalent to GJ4>3.
To return the error value N/A#, use the function NA().
To return N/A# as text, use #N/A#".
So the formula could be
=IF(GJ4>3, COUNTIFS(FY2:FY50, "<=4",FY2:FY50, ">=3"), NA())
or
=IF(GJ4>3, COUNTIFS(FY2:FY50, "<=4",FY2:FY50, ">=3"), "#N/A")
