CountIf help

[GoDawgs]

I've got a sheet with a list of open dates and close dates for stores. Basically I want a count of all stores open as of a certain month...seems easy enough. I set up a countif to give me the number of stores with open dates before the certain month, and then subtracted the stores with close dates before that same month. The problem is not all stores with close dates have open dates, so I can't subtract all of the stores with close dates, I need to make sure they have open dates as well. Basically I'd like to do something like dsum with dcount...I'd like to set up a criteria for one range, but count another range. Here's an example of what I'm talking about in case it's confusing:

open close
1/1/02
5/1/01
1/1/02 4/1/02

So if I'm checking for number of stores open as of today I would get 2 for the dcount of open and 2 for the dcount of closed...meaning there would be 2-2=0 stores open when there's really 1 open. Any help would be great.

Kevin

 

[GoDawgs]

One more thing...I also have some stores with no close or open dates (only "stores" in name really)...thought I better throw that in.

 

[xlbo]

Kevin - try this ARRAY formula - see if it works for you:
=SUM((A2:A4<&quot;31/01/02&quot*(IF(ISBLANK(A2:A4),0,1))*(IF(ISBLANK(B2:B4),1,0)))
where your data is in A:B
Thias should work for the latest date - ie counts those stores with no close date. If you want to look at a particular month, try this one:
=SUM((A2:A4<&quot;31/01/02&quot*(IF(ISBLANK(A2:A4),0,1))*(IF(ISBLANK(B2:B4),1,IF(B2:B4>&quot;31/01/02&quot;,1,0)))) - this is set up for Jan at the mo but change the dates to change the criteria
Both formulae need to be entered with CTRL + SHIFT + ENTER rather than just ENTER - you'll see {} round the formula if you've done it right
Rgds
~Geoff~

 

[DaleWatson123321]

Kevin,

I've created a model using the DCOUNTA function you referred to.

It works according to your specifications - i.e. it provides a count of all the stores open as of any given month.

There are two &quot;input&quot; cells, into which you can enter any month and year. The DCOUNT formula, assisted by other formula and criteria, then provides an accurate count.

The model of course is yours for the asking. Just email me and I'll send the file via return email.

IMPORTANT: Because I'm leaving for vacation Friday afternoon, email me ASAP so that I can email you the file before I leave.

Regards, ...Dale Watson

HOME: nd.watson@shaw.ca