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 turps,

There is nothing wrong with your formula, so it sounds like you have Calculation set to Manual. Either make sure you press F9 before checking the result of set it to Autonatic (see Tools > Options > Calculation tab)

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]

Tony

Thanks for your input but I have checked and the Calculation is set to Automatic.

Any other ideas?

David

 

[TonyJollans]

Hi David,

Nothing I can think of quickly, except .. you say you have another formula which sets the "Yes"es. Is that formula updating correctly all the time? Is that a UDF, or built-in?

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.

 

[xlbo]

If I refresh

Refresh what ??
What is your countif counting on ?? is it a pivottable ??

Rgds, Geoff

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

Please read FAQ222-2244 before you ask a question

 

[turps]

In reply to Tony
The Formula is a UDF and that works fine. What it does is looks to see if two conditions are meet and then either puts a 'Yes', or'No' in col F. This works fine.

This doesn't even if I manually put in a 'Yes'

In reply to Geoff

All the countif is look at is one col of data.

Regards David

 

[xlbo]

So what does the "Refresh" refer to ??

Try putting
Application.Volatile as the 1st line in your UDF

Rgds, Geoff

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

Please read FAQ222-2244 before you ask a question

 

[turps]

Sorry my fault it's not a UDF. It's all on the worksheet.

Regards

David

 

[xlbo]

So what does "refresh" refer to ?? you still havn't answered that

Lets see if we have this straight - please CHECK and CONFIRM if each of these statements is true

1: You have a workbook and the calculation is set to automatic (checked via going Tools>Options>Calculation) ??

2: Within this workbook you have a formula that produces a "Yes / No " answer ?? If yes, please post the formula

3: If above is Yes, what range does this formula fill ??
(please answer with something like F6:F400)

4: You have another formula that looks at the column the above formulae are in and does a "COUNTIF" on "Yes" ? If yes, please post the formula

5: When the formula in 2: changes, the formula in 4: does not ??

Please answer the above as your answers so far are confusing..

Rgds, Geoff

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

Please read FAQ222-2244 before you ask a question

 

[pbrodsky]

Are you sure you have "yes" in the cells you are checking -- not "yes " (extra blanks).

It works for me just the way you had it originally.

 

[turps]

Geoff
Ok sorry for being very clear.

What I mean by 'refresh' is if I press F9 then the correct total appears.
Naswer to the rest of the questions are:-

1 - Claulation is set to automatic

2 - The formula that produces the 'Yes/No' is in J5 and is
=IF(U6=0,"No","Yes")

3 - the range that I am looking in is J6:J4000

4 - the formula =COUNTIF(J6:J4000,"Yes")

5 - thats correc.

I hope that this all makes sense.

Regards


David

 

[xlbo]

If you press F9 and it re-calcs then your calculation is NOT set to automatic. F9 is the MANUAL RECALC button

Please re-check under Tools>Options>Calculation

Which option is marked ??

Rgds, Geoff

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

Please read FAQ222-2244 before you ask a question

 

[turps]

Hello Geoff

I have checked and the calculation is set to Automatic. This is so because all the other formula updates straight away.

The only reason for using F9 was to force the calculation through.



Regards

David

 

[xlbo]

Very odd - I am assuming that your =IF(U6=0,"No","Yes")
formula is copied down all the way to J4000 ??

One final Q - in your 1st post you stated F6:F4000 - it has now changed to J6:J4000

I'm not being facaetious but it mey be worth double checking that the formula is referring to the right range...

Rgds, Geoff

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

Please read FAQ222-2244 before you ask a question

 

[turps]

Sorry about the mistake in the first post it should have been J6:j4000. And the formula is copied all the way down.



Regards

David

 

[xlbo]

Ok - I'm officially stumped
I don't normally take things off-line but without seeing this, there is no more advice I can offer.

If you want me to have a look at it, you can email me the file @

GeoffNOSPAM dot BarracloughNOSPAM At PunchNOSPAMTavernsNOSPAM dot com

remove the NOSPAM and convert words to symbols

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,

Geoff has asked all the questions I would. He just typed quicker than me yesterday.

It doesn't stack up. If it's not confidential could you send me a copy of the workbook? - (my handle) at VBAExpress dot com.

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.

 

[xlbo]

Tony - the joys of a speedy work-line !!

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,

Thanks for the workbook. You have a circular reference in cell C4000 which stops calculation working properly. I haven't checked beyond that but try correcting it and see if it solves your 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.

 

[turps]

Hi Tony

Thanks for that. I have corrected it but the problem still persists.




Regards

David