Problem with countifs

Mazlet

New Member
Joined
Oct 15, 2012
Messages
10
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,

Thank you for your time.

Kind Regards,

Marie
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
=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
 
Upvote 0
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...........
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top