Using sumproduct to sum networkdays between dates with criteria?

[cheesysocks]

Hmmmm. Looks like I ruined my original posting while trying to mark it solved. Odd.

[Simon Lloyd]

Title changed!

[Richard Schollar]

Hello Mike

Why are you using the range G5:G400? Is it important that none of these cells contain the word "fly"?

Richard

[sglife]

I think this is what you need

Please Login or Register  to view this content.

*Press Ctrl + Shift + Enter.

[xld]

Try this

=(NETWORKDAYS(A8,L8))*(L8<>"")*(ISNUMBER(SEARCH("fly",G8)))

[cheesysocks]

Title changed!

OK, thanks.

[cheesysocks]

Hello Mike

Why are you using the range G5:G400? Is it important that none of these cells contain the word "fly"?

Richard

Column G is the description of a job a contractor does. It's to do with removing graffiti and fly-posting. Each row is a single incident and there will be a lot of rows in the sheet. (Each sheet is a weeks work, each book a months work.) I allow for up to 400 but we usually get about 150 / 200.

A description may be...

Tag "bloggs" in green on a concrete wall
Black felt pen on a phone box
Fly posters on a mesh fence
etc.

I need to seperate the fly-posting statistics from the graffiti statistics, so I need to know if the word fly is there or not.

I hope this makes a little sense.

[cheesysocks]

I think this is what you need

Please Login or Register  to view this content.

*Press Ctrl + Shift + Enter.

Thanks, I shall experiment with this. I'll let you know what happens. Or doesn't, whatever!

[cheesysocks]

Try this

=(NETWORKDAYS(A8,L8))*(L8<>"")*(ISNUMBER(SEARCH("fly",G8)))

Oops, nearly missed this reply.

Thanks, I will experiment with this one too. Thank you for the help.

[cheesysocks]

Thanks, I shall experiment with this. I'll let you know what happens. Or doesn't, whatever!

Please Login or Register  to view this content.

Slightly altered, but...

"Array formulas are not valid in merged cells". ARGGHH!

Unfortunately, the layout of the sheet means the cells the formula is in are merged. Without completely redesigning the sheets (a lot of work) I can't un-merge them.

There's always something, isn't there! But thanks, that would have worked I think.

[cheesysocks]

Try this

=(NETWORKDAYS(A8,L8))*(L8<>"")*(ISNUMBER(SEARCH("fly",G8)))

Tried, close but no cigar.

With the date reported (A8) and the date cleared (L8) being equal, it returns 'zero', as I need.

If I change the date cleared (L8) to a later date, the result is still 'zero' whereas I need the count of the working days between.

If I change the content of the text to include the word "fly" it returns 'one', whereas I need a blank cell to show.

[cheesysocks]

If anyone fancies looking , attached is a small, sample workbook.

[Richard Schollar]

By the sounds of it, you just need the reciprocal of the Search term in Xld's formula (and you can wrap it in an IF if required to diplay blank rather than 0):

=(NETWORKDAYS(A8,L8))*(L8<>"")*(1-ISNUMBER(SEARCH("fly",G8)))

If you'd rather have a blank if fly is found then:

=IF(OR(ISNUMBER(SEARCH("fly",G8)),L8=""),"",NETWORKDAYS(A8,L8))

Richard

[cheesysocks]

Sorry for the delay in replying Richard, been away for a few days.

I'll play with this formula over the next couple of days. I'll mark it up solved if it works! Thanks.

[cheesysocks]

Thanks for your input, but eventually I decided on nested if's.

The logic for a row containing a graffiti report, not fly-posting is...

IF L is blank, then this cell is blank.
ELSE IF "fly" is in G then this cell is blank.
ELSE work out how many days.

Please Login or Register  to view this content.

And the logic for a row with fly-posting, not graffiti is...

IF L is blank, then this cell is blank.
ELSE IF "fly" is in G then work out how many days.
ELSE this cell is blank.

Please Login or Register  to view this content.

So in effect I just swapped around the results in the else section of the second IF. It works for me.

Only 57 Days to Christmas.