Excel Stuck trying to use Excel 2007 Countifs.


Status
Not open for further replies.
Joined
Feb 1, 2012
Messages
2
Reaction score
0
Hi All,

I'm putting together a drawing register so I have two columns of dates. Column A is the forecast issue date and B is the actual issue date. It sounds simple but I need to count the number of dates in column A that fall within the current week AND that also have an issue date of anything before the start of this week. (a drg that is issued early to forecast)

It's the 'within this week' part i'm struggling with because to work that out you need two bits of logic (greater than the start of the week AND less than the end of the week) and I can't seem to nest that within one of the COUNTIFS criteria.

This is what i tried (D2 is 01/02/12):

=COUNTIFS(A1:A5,(AND(">="&$D$2-WEEKDAY($D$2,3),"<="&$D$2-WEEKDAY($D$2,3)+6)),B1:B5,"<"&$D$2-WEEKDAY($D$2,3))

A B
01/01/2011 01/01/2011
02/01/2011 02/01/2011
05/05/2011 05/05/2011
02/02/2012 01/12/2011
03/02/2012 30/01/2012

Any help greatly appreciated!
 
Ad

Advertisements

Status
Not open for further replies.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top