# Problem with countifs

#### Mazlet

##### New Member
Dear All,

I am having issues with this formula:

=IF(TODAY()<D10,"",IF(TODAY()<=D11,Register!\$W\$1,COUNTIFS(Register!\$D\$7:\$D\$3076,"<="&Record!D11,Register!\$U\$7:\$U\$3076,"")+COUNTIFS(Register!\$D\$7:\$D\$3076,"<="&Record!D11,Register!\$U\$7:\$U\$3076,">"&D11)-COUNTIFS(Register!\$D\$7:\$D\$3076,"<="&Record!D11,Register!\$U\$7:\$U\$3076,"<"&D10)))

I am trying to pull the number of open documents in the list between a set of given dates.

D10 = the 1st of a given month, e.g. 1/3/2013
D11 = the last date in a given month. e.g. 31/3/2013
Register!\$W\$1 = the current count of open documents listed
Register!\$D:\$D = Date document started/opened
Register!\$U:\$U = Date closed

So as I see it the formula above says:
1. If today if before the first date cell is blank
2. If today is before the last date cell equals the current count (and from this point it gets messy)
3. Count all rows where the document was opened before the last date and does not have a date closed (still open)
4. Plus Count all rows where the document was opened before the last date and was closed after the last date
5. minus Count all rows where the document was opened before the last date and was closed before the first date (closed before range)

The formula seems to work for last month but not for this month as the current count is 32 (and was at month end) but the cell reads 16. I don't know whether it's a problem with my formula or a step I have missed in the logic.......... Any help would be greatly appreciated,

Kind Regards,

Marie

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
=IF(TODAY()<D10,"",IF(TODAY()<=D11,Register!\$W\$1,COUNTIFS(Register!\$D\$7:\$D\$3076,"<="&Record!D11,Register!\$U\$7:\$U\$3076,"")+COUNTIFS(Register!\$D\$7:\$D\$3076,"<="&Record!D11,Register!\$U\$7:\$U\$3076,">"&D11)-COUNTIFS(Register!\$D\$7:\$D\$3076,"<="&Record!D11,Register!\$U\$7:\$U\$3076,"<"&D10)))

For some reason it didn't pst the whole formula

Since I can't seem to post the whole equation (not sure why):
1. If today if before the first date cell is blank =IF(TODAY()<D10,"", (see 2))
2. If today is before the last date cell equals the current count = IF(TODAY()<=D11,Register!\$W\$1, (see 3 - and from this point it gets messy)
3. Count all rows where the document was opened before the last date and does not have a date closed (still open) = COUNTIFS(Register!\$D:\$D,"<="&Record!D11,Register!\$U:\$U,"") + (see 4)
4. Plus Count all rows where the document was opened before the last date and was closed after the last date = +COUNTIFS(Register!\$D:\$D,"<="&Record!D11,Register!\$U:\$U,">"&D11) - (see 5)
5. minus Count all rows where the document was opened before the last date and was closed before the first date (closed before range) = - COUNTIFS(Register!\$D:\$D,"<="&Record!D11,Register!\$U:\$U,"<"&D10)))

I don't know whether that will help...........

Replies
4
Views
251
Replies
2
Views
159
Replies
1
Views
292
Replies
1
Views
155
Replies
3
Views
243

1,196,229
Messages
6,014,122
Members
441,802
Latest member
rhinab

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back