Black Pearl
New Member
- Joined
- May 14, 2008
- Messages
- 10
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:
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?
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
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?