CONDITIONAL / NESTED COUNTIF
 

I want to countif the occurrences between a certain average result/number in
my cell V66 and cero. Because my data would be changing over time, the
numbers of occurences will be higher or lower. How do I combine the following
to countif and always get a positive number?:

- If I use my formulae =COUNTIF(V5:V64,""&V66)-COUNTIF(V5:V64,"0"), I get
the correct number, but it is a negative number (e.g.-25)

- If I reverse the order in the formulae above:

=COUNTIF(V5:V64,"0")-COUNTIF(V5:V64,""&V66), I still get the correct
number and it is positive (e.g. 25).


Thank you

CONDITIONAL / NESTED COUNTIF
 

Nel post
*OrlandoFreeman* ha scritto:

I want to countif the occurrences between a certain average
result/number in my cell V66 and cero. Because my data would be
changing over time, the numbers of occurences will be higher or
lower. How do I combine the following to countif and always get a
positive number?:

- If I use my formulae =COUNTIF(V5:V64,""&V66)-COUNTIF(V5:V64,"0"),
I get the correct number, but it is a negative number (e.g.-25)

- If I reverse the order in the formulae above:

=COUNTIF(V5:V64,"0")-COUNTIF(V5:V64,""&V66), I still get the correct
number and it is positive (e.g. 25).


Thank you


Hi Orlando,

try with this:

=ABS(COUNTIF(V5:V64,""&V66)-COUNTIF(V5:V64,"0"))

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

CONDITIONAL / NESTED COUNTIF
 

Thank you Franz,

I tried your ABS formulae and seems to work fine. I also tried the following
and also works. Which formulae do U think would give the most as I have to
use and keep it up-to-date over 12 worksheets?


=IF(COUNTIF(V5:V64,""&V66)<COUNTIF(V5:V64,"0"),C OUNTIF(V5:V64,"0")-COUNTIF(V5:V64,""&V66),"")

I will try yours now.

"Franz Verga" wrote:

Nel post
*OrlandoFreeman* ha scritto:

I want to countif the occurrences between a certain average
result/number in my cell V66 and cero. Because my data would be
changing over time, the numbers of occurences will be higher or
lower. How do I combine the following to countif and always get a
positive number?:

- If I use my formulae =COUNTIF(V5:V64,""&V66)-COUNTIF(V5:V64,"0"),
I get the correct number, but it is a negative number (e.g.-25)

- If I reverse the order in the formulae above:

=COUNTIF(V5:V64,"0")-COUNTIF(V5:V64,""&V66), I still get the correct
number and it is positive (e.g. 25).


Thank you


Hi Orlando,

try with this:

=ABS(COUNTIF(V5:V64,""&V66)-COUNTIF(V5:V64,"0"))

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

CONDITIONAL / NESTED COUNTIF
 

Less function you use in a formula faster will be your formula and also, I
think that the one with ABS is more readable...


OrlandoFreeman wrote:
Thank you Franz,

I tried your ABS formulae and seems to work fine. I also tried the
following and also works. Which formulae do U think would give the
most as I have to use and keep it up-to-date over 12 worksheets?


=IF(COUNTIF(V5:V64,""&V66)<COUNTIF(V5:V64,"0"),C OUNTIF(V5:V64,"0")-COUNTIF(V5:V64,""&V66),"")

I will try yours now.

"Franz Verga" wrote:

Nel post
*OrlandoFreeman* ha scritto:

I want to countif the occurrences between a certain average
result/number in my cell V66 and cero. Because my data would be
changing over time, the numbers of occurences will be higher or
lower. How do I combine the following to countif and always get a
positive number?:

- If I use my formulae
=COUNTIF(V5:V64,""&V66)-COUNTIF(V5:V64,"0"), I get the correct
number, but it is a negative number (e.g.-25)

- If I reverse the order in the formulae above:

=COUNTIF(V5:V64,"0")-COUNTIF(V5:V64,""&V66), I still get the
correct number and it is positive (e.g. 25).


Thank you


Hi Orlando,

try with this:

=ABS(COUNTIF(V5:V64,""&V66)-COUNTIF(V5:V64,"0"))

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy