CountIf Question

[Guest]

Would someone tell me why the following doesn't work? Thank you.

=COUNTIF(Aliberti!G40:G53, ">=Aliberti!G6")

 

[Roger Govier]

Hi Jane

Try

=COUNTIF(Aliberti!G40:G53, ">="&Aliberti!G6)

Regards

Roger Govier

 

[Domenic]

Try...

=COUNTIF(Aliberti!G40:G53,">="&Aliberti!G6)

Hope this helps!

 

[Guest]

Thank you. It worked. Yeah!

Hi Jane

Try

=COUNTIF(Aliberti!G40:G53, ">="&Aliberti!G6)

Regards

Roger Govier

 

[Guest]

Thank you that worked. Would you tell me why the following won't work?
Thanks again.

=COUNTIF(Aliberti!$G$62:$G$74,
">="&Aliberti!$G$6*0.7)-COUNTIF(Aliberti!$G$62:$G$74,
"<="&Aliberti!$G$6*0.799)

 

[Domenic]

Try changing "<=" to ">" as follows...

=COUNTIF(Aliberti!$G$62:$G$74,">="&Aliberti!$G$6*0.7)-COUNTIF(Aliberti!$G
$62:$G$74,">"&Aliberti!$G$6*0.799)

Hope this helps!

 

[Guest]

Thank you again for your help. I entered your formula and played with it bu
I still can't get it to work Any ideas on what I'm doing?

=COUNTIF(Aliberti!$G$22:$G$31,">="&Aliberti!$G$6*0.001)-COUNTIF(Aliberti!$G$22:$G$31,">"&Aliberti!$G$6*0.699)

Also, this other formula is not working and it seems it should be so simple.

=COUNTIF(Aliberti!$G$40:$G$53, "<=0")

 

[Roger Govier]

Hi Jane
What error are you getting?
What does Aliberti!G6 contain?
You could try wrapping brackets around the multiplication in case the cell
contents are a text number

=COUNTIF(Aliberti!$G$22:$G$31,">="&(Aliberti!$G$6*0.001))-COUNTIF(Aliberti!$G$22:$G$31,">"&(Aliberti!$G$6*0.699))

Your other formula looks fine. Maybe try
=COUNTIF(Aliberti!$G$40:$G$53,"<="&0)


Regards

Roger Govier

 

[Guest]

Thank you for your help. I tried both formulas but I still can't get it to
work. the G6 is a $goal which changes in various columns so I am trying to
write my function so that I do not have to redo them every month. That is
why I need to multiply the goal with an upper and lower # to get an apples to
apples formulas.

I realize the problem with my second formula is that when I say <=0, I need
it to count blanks and negatives. Is there a way to do that? Thanks again.

 

[Roger Govier]

Hi Jane

=COUNTIF(Aliberti!$G$40:$G$53,"<="&0)+=COUNTIF(Aliberti!$G$40:$G$53,"<>")
will give you the blanks in addition to the zero and negatives.

I can't think of any other reason why the countif formulae shouldn't work.
If you want to mail me a copy of the sheet, I could take a look for you.
Remove NOSPAM from my email address to send.

You could always try

=SUMPRODUCT(--(Aliberti!$G$22:$G$31,>=(Aliberti!$G$6*0.001)),--(Aliberti!$G$22:$G$31>(Aliberti!$G$6*0.699))



Regards

Roger Govier

 

[Domenic]

Thank you for your help. I tried both formulas but I still can't get it to
work. the G6 is a $goal which changes in various columns so I am trying to
write my function so that I do not have to redo them every month. That is
why I need to multiply the goal with an upper and lower # to get an apples to
apples formulas.

Maybe your numbers are being recognized as text. What result do you get
when you try the following...

=COUNTIF(Aliberti!$G$22:$G$31,"?*")

I realize the problem with my second formula is that when I say <=0, I need
it to count blanks and negatives. Is there a way to do that? Thanks again.

Try the following formula instead...

=SUMPRODUCT(--(Aliberti!$G$40:$G$53<=0))