COUNTIF, SUMPRODUCT and ISBLANK help

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.:ROFLMAO:

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? :eek:
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Hi Aladin,

I got an answer of 436 when I should have got an answer of 20.

Black Pearl

Does this...
Code:
=SUMPRODUCT(
   --ISNUMBER('Investigations and Issues Log'!I3:I700),
   --('Investigations and Issues Log'!I3:I700<=D7),
   --('Investigations and Issues Log'!M3:M700=""))
yield the correct figure?
 

Black Pearl

New Member
Joined
May 14, 2008
Messages
10

ADVERTISEMENT

Oh my gosh! Yes, that is perfect! Wow! Yee hah! I've spent all day researching this and you just got it straight away.

Thanks so much for your help Aladin! :)
 

Black Pearl

New Member
Joined
May 14, 2008
Messages
10
Actually Aladin, there is one part missing.

Once a date is in column M, it interferes with the totals because it is no longer blank.

So I tried this.

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

Instead of getting an answer of 20 for that week, I keep getting 1.

Any ideas?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

Actually Aladin, there is one part missing.

Once a date is in column M, it interferes with the totals because it is no longer blank.

So I tried this.

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

Instead of getting an answer of 20 for that week, I keep getting 1.

Any ideas?

If a particular week is the target, try something like...
Code:
=SUMPRODUCT(
  --('Investigations and Issues Log'!I3:I700>=C7),
  --('Investigations and Issues Log'!I3:I700<=D7),
  --('Investigations and Issues Log'!M3:M700=""))

where C7 and D7 together specifies the week of interest.
 

Black Pearl

New Member
Joined
May 14, 2008
Messages
10
Thanks for that, I gave it a go, bit still not working.

However, say a job was logged on 01/10/08 (column I), and it only moved into the 'currently working' phase (column M) on 07/11/08. Then for the week ending the 31/10/08, I'd like to see that that job was still not actioned.

At the moment with the current formula, once I put the 07/10/08 into Column M, it disappears from the number of jobs still to be actioned as at the 31/10/08.

mmmmmmmmmm
 

Black Pearl

New Member
Joined
May 14, 2008
Messages
10
At the moment with the current formula, once I put the 07/10/08 into Column M, it disappears from the number of jobs still to be actioned as at the 31/10/08.

Th above should say: ....once I put the 07/11/08 into Column M....

Anyway, I this is where I'm up to:

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

It is still not quite right. mmmmmmmm
 

Black Pearl

New Member
Joined
May 14, 2008
Messages
10
Actually, it is correct. I had a few dodgy data cells with the incorrect dates and typo's. So all is well!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,657
Messages
5,597,391
Members
414,142
Latest member
Banyangt

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
Top