Re-use conditional part of IF function in positive expression?

[cgcamal]

Hi everyone,

I hope be clear enough.

I have this data in column A and B:

A|B
----
A    12
A    13
B    12
A    15
B    9
A    17

and I want to count how many A's are lower than 10.
I'm using this function:

=IF([COLOR=Red][B]COUNTIFS(B9:B14,"A",C9:C14,"<10")[/B][/COLOR],[COLOR=Blue][B]COUNTIFS(B9:B14,"A",C9:C14,"<10")[/B][/COLOR],"All  A's>10")

You may see that within the formula, I'm using twice this part:

COUNTIFS(B9:B14,"A",C9:C14,"<10")

This means the formula is of the form:

=IF([COLOR=Red][B]X[/B][/COLOR],[COLOR=Blue][B]X[/B][/COLOR],"Text")

where first X=Condition=">0",
second X=Positive part of IF(), and
"Text" is the negative part of IF()

Because I want to print the count, only if the count is lower than 10, if not I want to print "All A's>10"

This function works fine, I have solved this part, but my question is regarding other issue.

Within the same IF() formula, is possible avoid to write twice the same part if we need to use it
as condition
and as positive expression?
In this example avoid write twice X, because X sometimes could be very very large expression.

I mean, some shorter symbol that stores the previous part to use it later, something like
X=COUNTIFS(B9:B14,"A",C9:C14,"<10")
Y="value of X"
and reduce formula from this

=IF([COLOR=Red][B]COUNTIFS(B9:B14,"A",C9:C14,"<10")[/B][/COLOR],[COLOR=Blue][B]COUNTIFS(B9:B14,"A",C9:C14,"<10")[/B][/COLOR],"All  A's>10")

to this:

=IF([COLOR=Red][B]COUNTIFS(B9:B14,"A",C9:C14,"<10")[/B][/COLOR],[COLOR=Blue][B]Y[/B][/COLOR],"All  A's>10")

?

Many thanks in advance for any help.

Best regards

 

[Aladin Akyurek]

Add the following code to your workbook as a module...

Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function

and invoke:

=IF(V(COUNTIFS(B9:B14,"A",C9:C14,"<10")),V(),"All A's>10")

If you have Longre's morefunc.xll add-in...

=IF(SETV(COUNTIFS(B9:B14,"A",C9:C14,"<10")),GETV(),"All A's>10")

 

[cgcamal]

Hi Aladin,

Many thanks for the reply and for show me a solution that works exactly like I asked.

Thanks again.

Best regards

 

[Aladin Akyurek]

Hi Aladin,

Many thanks for the reply and for show me a solution that works exactly like I asked.

Thanks again.

Best regards

You are welcome. Thanks for providing feedback.