Help with calculation between work days based on condition

[jedilefty]

Hello!

I'm trying to calculate work days between two dates based on certain criteria met. The original formula I was using is as such:

=IF(E15=$A$2,M15-$B$2,IF(E15=$A$3,M15-$B$3,IF(E15=$A$4,M15-$B$4,IF(E15=$A$5,M15-$B$5,IF(E15=$C$2,M15-$D$2,IF(E15=$C$3,M15-$D$3,IF(E15=$C$4,M15-$D$4,IF(E15=$C$5,M15-$D$5,IF(E15=$C$6,M15-$D$6,IF(E15=$C$7,M15-$D$7,IF(E15=$C$8,M15-$D$8,IF(E15=$C$9,M15-$D$9,IF(E15=$C$10,M15-$D$10,IF(E15=$C$11,M15-$D$11,IF(E15=$C$12,M15-$D$12,IF(E15=$E$2,M15-$F$2,IF(E15=$E$3,M15-$F$3,IF(E15=$E$4,M15-$F$4,IF(E15=$E$5,M15-$F$5,IF(E15=$E$6,M15-$F$6,IF(E15=$E$7,M15-$F$7,IF(E15=$E$8,M15-$F$8,IF(E15=$E$9,M15-$F$9,IF(E15=$E$10,M15-$F$10,IF(E15=$E$11,M15-$F$11,IF(E15=$E$12,M15-$F$12,IF(E15=$E$13,M15-$F$13)))))))))))))))))))))))))))

But this would include weekends and I want to exclude weekends.

I tired modifying this formula to this:

=IF(E15=$A$2,(NETWORKDAYS(M15,$B$2,$G$3:$G$4)-SIGN(NETWORKDAYS(M15,$B$2,$G$3:$G$4)),IF(E15=$A$3,(NETWORKDAYS(M15,$B$3,$G$3:$G$4)-SIGN(NETWORKDAYS(M15,$B$3,$G$3:$G$4),IF(E15=$A$4,(NETWORKDAYS(M15,$C$4,$G$3:$G$4)-SIGN(NETWORKDAYS(M15,$C$4,$G$3:$G$4),IF(E15=$A$5,(NETWORKDAYS(M15,$B$5,$G$3:$G$4)-SIGN(NETWORKDAYS(M15,$B$5,$G$3:$G$4),IF(E15=$C$2,(NETWORKDAYS(M15,$D$2,$G$3:$G$4)-SIGN(NETWORKDAYS(M15,$D$2,$G$3:$G$4),IF(E15=$C$3,(NETWORKDAYS(M15,$D$3,$G$3:$G$4)-SIGN(NETWORKDAYS(M15,$D$3,$G$3:$G$4),IF(E15=$C$4,(NETWORKDAYS(M15,$D$4,$G$3:$G$4)-SIGN(NETWORKDAYS(M15,$D$4,$G$3:$G$4),IF(E15=$C$5,(NETWORKDAYS(M15,$D$5,$G$3:$G$4)-SIGN(NETWORKDAYS(M15,$D$5,$G$3:$G$4),IF(E15=$C$6,(NETWORKDAYS(M15,$D$6,$G$3:$G$4)-SIGN(NETWORKDAYS(M15,$D$6,$G$3:$G$4),IF(E15=$C$7,(NETWORKDAYS(M15,$D$7,$G$3:$G$4)-SIGN(NETWORKDAYS(M15,$D$7,$G$3:$G$4),IF(E15=$C$8,(NETWORKDAYS(M15,$D$8,$G$3:$G$4)-SIGN(NETWORKDAYS(M15,$D$8,$G$3:$G$4),IF(E15=$C$9,(NETWORKDAYS(M15,$D$9,$G$3:$G$4)-SIGN(NETWORKDAYS(M15,$D$9,$G$3:$G$4),IF(E15=$C$10,(NETWORKDAYS(M15,$D$10,$G$3:$G$4)-SIGN(NETWORKDAYS(M15,$D$10,$G$3:$G$4),IF(E15=$C$11,(NETWORKDAYS(M15,$D$11,$G$3:$G$4)-SIGN(NETWORKDAYS(M15,$D$11,$G$3:$G$4),IF(E15=$C$12,(NETWORKDAYS(M15,$D$12,$G$3:$G$4)-SIGN(NETWORKDAYS(M15,$D$12,$G$3:$G$4),IF(E15=$E$2,(NETWORKDAYS(M15,$F$2,$G$3:$G$4)-SIGN(NETWORKDAYS(M15,$F$2,$G$3:$G$4),IF(E15=$E$3,(NETWORKDAYS(M15,$F$3,$G$3:$G$4)-SIGN(NETWORKDAYS(M15,$F$3,$G$3:$G$4),IF(E15=$E$4,(NETWORKDAYS(M15,$F$4,$G$3:$G$4)-SIGN(NETWORKDAYS(M15,C15,$G$3:$G$4),IF(E15=$E$5,(NETWORKDAYS(M15,$F$5,$G$3:$G$4)-SIGN(NETWORKDAYS(M15,$F$5,$G$3:$G$4),IF(E15=$E$6,(NETWORKDAYS(M15,$F$6,$G$3:$G$4)-SIGN(NETWORKDAYS(M15,$F$6,$G$3:$G$4),IF(E15=$E$7,(NETWORKDAYS(M15,$F$7,$G$3:$G$4)-SIGN(NETWORKDAYS(M15,$F$7,$G$3:$G$4),IF(E15=$E$8,(NETWORKDAYS(M15,$F$8,$G$3:$G$4)-SIGN(NETWORKDAYS(M15,$F$8,$G$3:$G$4),IF(E15=$E$9,(NETWORKDAYS(M15,$F$9,$G$3:$G$4)-SIGN(NETWORKDAYS(M15,$F$9,$G$3:$G$4),IF(E15=$E$10,(NETWORKDAYS(M15,$F$10,$G$3:$G$4)-SIGN(NETWORKDAYS(M15,$F$10,$G$3:$G$4),IF(E15=$E$11,(NETWORKDAYS(M15,$F$11,$G$3:$G$4)-SIGN(NETWORKDAYS(M15,$F$11,$G$3:$G$4),IF(E15=$E$12,(NETWORKDAYS(M15,$F$12,$G$3:$G$4)-SIGN(NETWORKDAYS(M15,$F$12,$G$3:$G$4),IF(E15=$E$13,(NETWORKDAYS(M15,$F$13,$G$3:$G$4)-SIGN(NETWORKDAYS(M15,$F$13,$G$3:$G$4))))

With the modified formula, I get the error message either too many arguments, so I'll remove some parenthesis and I 'll then get too few arguments.
I have attached a screenshot of the layout of my spreadsheet (currently working of a company computer and I cannot install the software needed to upload my spreadsheet). If the values in cells E15 and below meet the criteria in either of the highlighted cells, then I want to subtract the values in the cells M15 and below from the cells next to the highlighted ones.

Can somebody help me out with getting this formula corrected or help if there is a better way of doing this?

 

[t0ny84]

What is the criteria you are using?

t0ny84

 

[Fluff]

How about

=NETWORKDAYS(M15,SUMIFS($B$2:$F$13,$A$2:$E$13,E15),$G$3:$G$4)

 

[jedilefty]

What is the criteria you are using?

t0ny84

The criteria is in the highlighted cells. So if the values in the cells E15 and below have any of the values in the highlighted cells (i.e. A, B, C (also it will only be one value per cell)). Then I want to subtract the value in "Finish Date" column from the cell next to the highlighted cell.

Example: If the value in E15 is "W", and date in Finish Date column is 1/15/24, Then I'd like to subtract the number in F9 from the 1/15/24 date, but only calculate working days. Does this make sense?

 

[jedilefty]

How about

=NETWORKDAYS(M15,SUMIFS($B$2:$F$13,$A$2:$E$13,E15),$G$3:$G$4)

Hi Fluff. This one didn't work out. It gives multiple results.

Here is an example of the results I'm trying to get:

Example: If the value in E15 is "W", and date in Finish Date column is 1/15/24, Then I'd like to subtract the number in F9 from the 1/15/24 date, but only calculate working days. Does this make sense?

 

[Fluff]

Maybe

=WORKDAY(M15,-SUMIFS($B$2:$F$13,$A$2:$E$13,E15),$G$3:$G$4)

 

[jedilefty]

Maybe

=WORKDAY(M15,-SUMIFS($B$2:$F$13,$A$2:$E$13,E15),$G$3:$G$4)

Apologies for the late response, but this worked!! Thanks Fluff!!

 

[Fluff]

You're welcome & thanks for the feedback.