We help IT Professionals succeed at work.

Add total duration of the weeks

Billa7
Billa7 used Ask the Experts™
on
I need Experts help to add additional function in the attached Sumproduct formula. Currently data from Column D  cell is (Detail sheet) individually subtract at column E. How to make sure the subtraction value is actually a total sum of total duration of the weeks. E.g. Cell E3 need to subtract based on total duration of cell D2 and D3 (34:00 + 24:00), same goes if we need to count result at cell E4, total duration of (D2:D4) need to subtract. Hope Experts could help to add this function.  

=IF(TODAY()<$B2,"",(D2-(SUMPRODUCT((Data!$B$2:$B$331<=$C2)*(Data!$D$2:$D$331=E$1)*(Data!$I$2:$I$331="Yes")*Data!$H$2:$H$331))))

Open in new window

Formula---Copy.xls
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
=IF(TODAY()<$B2,"",(SUM($D2:$D$2)-(SUMPRODUCT((Data!$B$2:$B$331<=$C2)*(Data!$D$2:$D$331=E$1)*(Data!$I$2:$I$331="Yes")*Data!$H$2:$H$331))))

Author

Commented:
Thanks a lot