NETWORKDAYS + SUMPRODUCT Formula

Hello guys,

Thanks in advance for your help, I'm learning a lot through this forum.

I already have this formula that counts how many cells I have that meet these 2 criterias.

=SUMPRODUCT(--(Table2[Milestones]="blabla"),--((Table2[Date]>0)))

But I want to have the Average number of days between all the dates that meet the criteria "blabla" and are not empty and all the dates that meet the criteria "lolo" and are not empty.

It has to be an Array formula because I have multiple times the Milestone "blabla" and the milestone "lolo".


Hope it'S clear, thanks a lot for your input.

Cheers.


Guillaume

work template 2.xlsx

Here you go. Usually the partners names are auto populated through another sheet that has the contacts.

So I want to evaluate the average number of days between each SAlt in a box sign up and Nexus survey.


Thanks a lot for the help.

-Guillaume

Hi Daddylonglegs,

Thank you really much for the formula ! I couldn't have found that on my own.

Is there a way that you can modify it to Evaluate the Average Number of Days between Salt-in-a-box sign up and The Nexus survey Milestone ? It is actually the repeated difference between those 2 days that I want to know. Not so much the average # of days between each Salt-in-a-box sign up.

Million thanks for your help.

-Guillaume

Hi guys,


Does anyone has an idea ?

The formula from DaddyLonglegs is good but has to be adapted a little bit I guess. The average # of days between SALT-in-a-Box sign up and the The nexus survey on a repetitive basis.

Thank you for your help.

-Guillaume

What do you mean by that ?

If I enter some fields manually ? => I'll put the date for each step manually. BUT the field Partner Name is a formula linked to another sheet and the Milestones are also a Formula based on the cell right next to it in the column before.


I don't know if that answers your question so please don't hesitate if you want further explanations...

Thanks a lot!!

Yes of course!

So by calculating the average Number of days between the Milestone 1 (SALT-in-a-Box sign-up) and the Milestone 3 ( The nexus survey), I want to be able with that formula, to evaluate on average how many days does a partner take to go from Milestone 1 to Milestone 3.

I will have multiple partners and thus I want the formula to consider every difference between those milestones.

The date will be put manually once the Milestone is completed by the partner.

So fort example by a quick look at the answer from the formula, I'll be able to say : On average, Partners take 40 days between those 2 steps.

Hope that helps, thanks a lot for your time.

-Guillaume

work template 3.xlsx

Please see attached for an example. It may just have 3 partners but you'll see the different step to get the final results... So I cannot use the same process for a whole table.

The result in that case is 164 days. Average Number of days between SALT in a Box sign up and the Nexus survey.


Hope that's the thing you needed, if it's not let me know, I'll try to put up another example.

Thanks a lot I've been struggling a lot on that one.


-Guillaume

Do you need more information XOR LX ?

I can try to provide more info.

Thanks a lot.

Yes it will be always the same. It's a pipeline process.

Thank you really much

Yes it is. It's a pipeline process.

Thank you really much

Actually, I'll be happy with the average # of working days or the average # of days between the 2 Milestones.

I used Networkingdays just because I thought about this formula and I also thought that it was easier...

Whatever you have, I'll take it

Thanks !

Yes they are... For each partner...

It's like the dates start over when it's a new partner. Each Partner will complete the Milestones in a different Timeline. One can go from step 1 to 5 in 1 month ( september to october), the other one could go from step 1 to step 5 in 6 month ( June to November).

So they go in ascending order but for each partner and NOT when we compare a partner to the other one.

Hope that's clear for you, I know that you are trying to help me and I appreciate that a lot.

Thanks !

I understand...

Thanks for your help ! I thought that Daddylongsleg had it but it was comparing the SALT-in-a-Box together.

XOR LX, I guess you're almost done ! lol sometimes I can manipulate a little bit the formula if it shows an error but this I can't...

I have the #NUM! error. I've look and the references look good...

Sorry for that, I believe it's a really tough one. I wasn't expected that.


Million thanks !

Indeed yes.. I made sure by re-opening the document, copy-pasting, but it still says #NUM!

Yep.

Do you mind posting your document ? I'll take a look real quick and I'll let you know if it works on my side.

Thanks thanks !

work template 4.xlsx

Here you go.

I look forward to seeing what's wrong.

Thanks !

Thank you really much for your help for this formula. I couldn't imagine finding that on my own.

Is there a way that if the date for the Nexus Survey is not put, it doesn't consider it and just calculate the difference between SALT in a box sign up and Nexus survey for the partners before ?

Because right now, it says #Value if I don't put a date into the last Nexus survey and thus I have to adapt the formula all the time with the precise range.


So to be more precise, I don't want to calculate the difference if the Nexus Survey is not put. I don't want the formula to consider the partners that don't have completed the Nexus Survey.

Maybe that's impossible with the formula up there. In any case, thanks a lot !

Guillaume

Maybe I can combine it with something like that : =IF(MOD(ROWS($1:9)-1,7)=0

What do you think ?

Best regards.