COUNTIF, SUMPRODUCT and ISBLANK help

[Black Pearl]

Hi there,

Hopefully this is not too hard, but it is driving me banana's.

I have a spreadsheet that I use to track hardware issues.

There are a number of columns, of which there is:

• Column I - Date Logged
• Column M - Date Currently Working (which is the date work started on this issue)
• Column O - Date Completed

So far, I can easily work out how many jobs were logged, how many moved into the 'currently working' phase, and how many were completed in each week.

I used the following to count how many were logged each week:
=COUNTIF('Investigations and Issues Log'!I3:I700,">=" & C7)-COUNTIF('Investigations and Issues Log'!I3:I700,">" & D7)

where C7 is the 'Week Starting' date and D7 is the 'weekending' date on a different sheet. And it worked great.

However, I'm trying to create a graph that shows how many jobs are unactioned and currently working at the end of each week, regardless of how old they are.

So, I want to know how many jobs are currenlty 'unactioned' at the end of the week, where the date is less than the 'weekending' date and there is no data in the 'currently working' column (it's blank). And I also want to know how many jobs are in the 'currently working' phase where there is no data in the 'completed' column (it's blank).

I've tried:

=SUMPRODUCT(--('Investigations and Issues Log'!I3:I700<=D7),--('Investigations and Issues Log'!M3:M700=""))

but it didn't work. And I also tried combining with an ISBLANK but I kept getting errors.

Any ideas?

 

[Aladin Akyurek]

Actually, it is correct. I had a few dodgy data cells with the incorrect dates and typo's. So all is well!

If so, you don't need the last -- bit because the + operator takes over its role.