Calculate matching cells

  • Thread starter Thread starter sharder
  • Start date Start date
S

sharder

Hi All,
I have a challange that I know you can help with. I am trying to find
the networkdays between D2 & D3 if A3 matches A2. Can you help?
A B C D
478630 N512A 010 6/23/05
478630 N505A 020 6/23/05
478630 N524A 030 6/24/05
478630 N547A 040 6/27/05
478633 N512A 010 6/23/05
478633 N505A 020 6/23/05
478633 N524A 030 6/23/05
478633 N547A 040 6/27/05
478633 S561A 050 6/27/05
478633 S561A 060 6/27/05
478633 S464A 080 7/11/05
478633 S250A 090 7/9/05
478633 S434A 100 7/15/05
478633 S446A 120 7/15/05
478636 N512A 010 6/23/05
478636 N505A 020 6/24/05
478636 N524A 030 6/24/05
478636 N547A 040 6/27/05
Thanks
sharder
 
in cell e3, type
=IF(A3=A2,NETWORKDAYS(D2,D3),"")
if you want to add holidays to that, you can add a comma and range after the
d3 and before the end paren. - the range showing holiday dates.
You probably should sort by column a and then by d - otherwise you could end
up with negative numbers as is one of the cases here.

hth,
Dave
 
Here is an alternative

=IF(A2=A3,ABS(NETWORKDAYS(D2,D3))-(D2=D3),"")

ABS .... will always give you the positive value
-(D2=D3) .... if both values are a Friday --> 0 days instead of 1 day
...or adjust to the way you want it to be

Hope it helped
Ola Sandström
 
Back
Top