Excel COUNTIF function

[jnix]

When I try to use the countif function between workbooks, that is the countif function in one workbook is counting data in another workbook, I get a VALUE# error unless both workbooks are open. Is there any way around this?
Thanks,
Jnix

 

[ETID]

Are you counting values that are results of formulas?

 

[jnix]

ETID
Yes.

 

[MJPPaba]

Hi ETID,

When the spreadsheet that is being counted is closed, what is the formula in the =countif(...) cell?

Matthew

 

[MJPPaba]

Hi Jnix,

My apologies ETID I meant to ask Jnix...

Matthew

 

[xlbo]

In general formulae will not update unless both workbooks are open. Once entered and calculated, the next time you open the workbook with the formulae, you will be asked if you want to update links. If the other wb is not open, choose No. This should preserve your numbers until you open the other wb and the formulae can update....

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[SQLScholar]

xlbo,

I am having the same problem... so why can i counta, but not count if. Is there any other way arround this problem?

Dan

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[ETID]

Have a look at the dcount() function...

 

[SQLScholar]

Trying Dcount - cause it will sort alot of problems.

I am trying:

=DCOUNT('[XM AUGUST 2004.xls]Renewal control sheet'!$F$4:$F$65536,,"YES")

and i am getting back a #value - any ideas?

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[ETID]

Here is an example, it's using the daverage function but the principle is the same for dcount

'***********************

If your data is in cells A1:C15

Name Sales Date
Smith 10 1/11/2004
Jones 20 1/12/2004
Miller 5 1/13/2004
Johnson 2 1/14/2004
Smith 5 1/15/2004
Jones 8 1/11/2004
Smith 20 1/12/2004
Johnson 12 1/13/2004
Smith 15 1/14/2004
Smith 6 1/15/2004
Miller 22 1/11/2004
Johnson 11 1/13/2004
Smith 9 1/15/2004
Jones 3 1/17/2004

then in cells(A18:C19)...type this, note: (this criteria definition can be typed any where), I just selected a18:c19
...here is where you can change the search criteria on the fly.

Name Sales Date
Smith >1/13/04

then to average the sales of Smith that occured after 1/13/04, the formula in a cell of your choice

=DAVERAGE(A1:C15,"Sales",A18:C19)