Sep 16 2021 06:33 AM
Hello,
I have the following tables:
Table 1 :
Project I/D | Name | Project Criticality | Start period 1 (yyyyww) | End period 1 (yyyyww) | Hours work/week period 1 | Start period 2 (yyyyww) | End period 2 (yyyyww) | Hours work/week period 2 |
1 | John Smith | 1 | 202101 | 202143 | 5 | 202144 | 202203 | 7 |
2 | Karen Smith | 2 | 202101 | 202202 | 4 | 202203 | 202304 | 5 |
3 | John Smith | 2 | 202110 | 202113 | 2 | 202114 | 202119 | 3 |
4 | Karen Smith | 1 | 202110 | 202202 | 1 | 202203 | 202245 | 2 |
Table 2:
Period |
2101 |
2102 |
2103 |
2104 |
2105 |
2106 |
2107 |
2108 |
2109 |
2110 |
2111 |
2112 |
2113 |
2114 |
2115 |
2116 |
2117 |
2118 |
2119 |
2120 |
2121 |
2122 |
2123 |
2124 |
2125 |
2126 |
2127 |
2128 |
2129 |
2130 |
2131 |
2132 |
2133 |
2134 |
2135 |
2136 |
2137 |
2138 |
2139 |
2140 |
2141 |
2142 |
2143 |
2144 |
2145 |
2146 |
2147 |
2148 |
2149 |
2150 |
2151 |
2152 |
2201 |
2202 |
2203 |
2204 |
2205 |
2206 |
22.. |
On table 2 , I want to add new columns to calculate the SUM of working hours per period and per project criticality. I believe SUMIFS would be the formula to generate these new values., but I have some trouble to do it... Can you please help on the formula to use to generate these new columns?
Period | SUM Hrs / Week C1 John Smith | SUM Hrs / Week C2 John Smith | Hrs / Week C1 Karen Smith | Hrs / Week C2 Karen Smith |
2101 | ||||
2102 | ||||
2103 | ||||
2104 |
OR is there any other suggestiont to do this better?
The file is enclosed as well.
Kind Regards,
Sep 16 2021 11:04 AM
In B5 on New Table:
=SUMIFS('Table 1'!$F$2:$F$5000,'Table 1'!$B$2:$B$5000,B$1,'Table 1'!$C$2:$C$5000,B$2,'Table 1'!$D$2:$D$5000,"<="&200000+$A5,'Table 1'!$E$2:$E$5000,">="&200000+$A5)+SUMIFS('Table 1'!$I$2:$I$5000,'Table 1'!$B$2:$B$5000,B$1,'Table 1'!$C$2:$C$5000,B$2,'Table 1'!$G$2:$G$5000,"<="&200000+$A5,'Table 1'!$H$2:$H$5000,">="&200000+$A5)
Fill to the right to E5, then fill down.
Sep 16 2021 02:20 PM
Sep 16 2021 02:43 PM
So there is no start date anymore? See the attached version. I changed the layout of Table3 slightly.