Tracking due date depending on conditions

**Abhijit2011** · 11-28-2011, 09:01 AM

Hi
I need help regarding one of my report. Sorry I’m not able to attach file from office but I’m trying my best to explain the requirements.
I’ve one data sheet which is huge but for simplicity let’s say there are 4 columns Project No (combination of text and no)., Defects (text) , Doc Required by Date (date format) and Doc Received? (Yes or No)
Altogether I have 9 projects (Say P111 to P119) each having 100s of defects and for each defect there is a Doc required by date.
Now I need to count for each project how many documents are pending within next 7 days. Also I need to take into account that “Document Received” entry should be “No”. If it is “Yes” it should not be taken into account.
So what I think it’s actually a count if formula having 3 condition one for project no, one for due date and the last one for doc received = no. But I’m not able to implement this

.
Any help will be much appreciated.

P.S although I’m using Office 2007 but I want it to be compatible with 2003 as well.

**brokenbiscuits** · 11-28-2011, 09:19 AM

To be compatible with 2003 you'll need to use SUMPRODUCT.

Something along the lines of:

=SUMPRODUCT(--(A2:A6=E2),--(C2:C6<(TODAY()+7)),--(D2:D6="No"))

where E2 contains the Project number you wish to check.

**Abhijit2011** · 11-28-2011, 12:05 PM

Excellent!! BrokenB. It's working perfectly.....Many thanks.
I've 2 queries though
1. Could you please explain the use of -- in the formula?
2. For Office 2007 is there any other solution available?

**brokenbiscuits** · 11-28-2011, 12:45 PM

There's a decent tutorial here which answers both points, the double negation and the use of SUMIFS:

http://techtites.com/2008/05/22/exce...le-conditions/

**Abhijit2011** · 11-28-2011, 01:17 PM

thanks...
I had one problem. I've sent the report and those having 2003 in their system told me that It's coming as #NUM! instead of any data. For 2007 it is fine. Any idea what's the problem is?

**brokenbiscuits** · 11-28-2011, 01:36 PM

Are you using whole column references? These won't work for SUMPRODUCT in older versions.

i.e. instead of using "C:C" you would need to use "C1:C2000" or whatever.