Countif Function not working properly

[turps]

Dear All

I have the countif function underneath deadline met which is =countif(f6:f400, "Yes").

I have another forumla that puts either a "Yes" or "No" in col F. When a "Yes" is put in col f I want the formula to count how many there are. The problem is that the formula does not count when a Yes is add in. If I refresh it counts when I then put in another Yes it does not count again. If I delete all the Yes's the count shows as 1 instead of 0.

Does anyone know why??

Thanks

David

Deadline Met? Originals
Yes 0 No 0 Filed Return Shred

 

[TonyJollans]

Hi David,

I'm afraid I can't get it to go wrong so it seems likely it is something in your environment (and manual calculation is the obvious thing) - can you test on another machine at all?

It is, however, very slow to calculate - are you sure you're giving it enough time to come up with its answers? Also I see you have lots of merged cells for no obvious reason - they shouldn't cause a problem but they are horrible things and best avoided. Other than that I'm not sure I can help much more .. sorry.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[TonyJollans]

Hi David,

I just realised you have circular references in the whole of columns A and C (at least). I would make sure the worksheet is sound before worrying about anything else.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[turps]

Hello

What I have done is added this sub routine in so that if any anything on the sheet changes the formula is updated.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Calculate

End Sub

This seems to work.




Regards

David

 

[xlbo]

You really should sort out the circular references tho - they WILL cause you more problems

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[TonyJollans]

Hi David,

I have now, I think, worked out what happens and the circular references are the problem. What are you trying to achieve with them?

Can you not replace ..

[purple][tt] =IF(AND(A18="",NOT(ISBLANK(G18))),NOW(),IF(ISBLANK(G18),"",A18))[/tt][/purple]

.. in A18, with this ..

[blue][tt] =IF(ISBLANK(G18),"",NOW())[/tt][/blue]

And similarly for the ones in column C.

I think it will do what you want. It will also make the sheet a whole lot faster and should sort out your COUNTIF problem.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.