I have the following data in a file with the Name field located in cell A1 and 1/1/2022 in cell E1. I have the following formula in cell E3: =IF($C3<E$1,0,SUM(MAX(NETWORKDAYS(MAX($B3,E$1),MIN(EOMONTH(E$1,0),$C3)),0)*8))*$A$35. This is pointing to the rate in cell A35. This formula is calculating the networkdays between the start and end dates in Columns B&C if they fall within the monthly columns starting in Col E. It then takes those hours and multiplies that by 8 and then multiplies it by the rate to give me a monthly cost. I'm looking for a formula that will add each columns hours. Any help would be appreciated.
**The attached file shows this same data and has cell E31 highlighted yellow which is where I need the formula. Thanks! (I attached the file but can't see it prior to posting so if it doesn't come through I'll try again to attach it.)
Name Start End 1/1/2022 2/1/2022 3/1/2022 4/1/2022 5/1/2022
1/31/2022 2/28/2022 3/31/2022 4/30/2022 5/31/2022
Resource 1 1/17/22 12/31/23 8,800 16,000 18,400 16,800 17,600
Resource 2 1/1/22 12/31/23 16,800 16,000 18,400 16,800 17,600
Resource 3 1/1/22 12/31/23 16,800 16,000 18,400 16,800 17,600
Resource 4 1/1/22 12/31/23 16,800 16,000 18,400 16,800 17,600
Resource 5 1/1/22 12/31/23 16,800 16,000 18,400 16,800 17,600
Resource 6 2/24/22 12/31/23 - 2,400 18,400 16,800 17,600
Resource 7 1/1/22 12/31/23 16,800 16,000 18,400 16,800 17,600
Resource 8 1/1/22 12/31/23 16,800 16,000 18,400 16,800 17,600
Resource 9 1/1/22 12/31/23 16,800 16,000 18,400 16,800 17,600
Resource 10 7/15/22 12/31/23 - - - - -
Resource 11 1/1/22 12/31/23 16,800 16,000 18,400 16,800 17,600
Resource 12 1/1/20 12/31/23 16,800 16,000 18,400 16,800 17,600
Resource 13 5/15/22 12/31/23 - - - - 9,600
Resource 14 1/15/22 12/31/23 8,800 16,000 18,400 16,800 17,600
Resource 16 1/1/22 6/30/22 16,800 16,000 18,400 16,800 17,600
Resource 17 6/15/22 12/31/23 - - - - -
Resource 18 1/1/22 6/30/23 16,800 16,000 18,400 16,800 17,600
Resource 19 5/1/22 12/31/23 - - - - 17,600
Resource 20 1/1/22 12/31/22 16,800 16,000 18,400 16,800 17,600
Resource 22 1/1/22 12/31/23 16,800 16,000 18,400 16,800 17,600
Resource 23 4/15/22 12/31/23 - - - 8,800 17,600
Resource 24 4/15/22 12/31/23 - - - 8,800 17,600
Resource 25 1/1/22 12/31/23 16,800 16,000 18,400 16,800 17,600
Resource 26 1/1/22 12/31/23 16,800 16,000 18,400 16,800 17,600
- - - - -
Total 1/1/21 12/31/22 NEED FORMULA HERE
Rate
100
Bookmarks