SUMIFS help

%3CLINGO-SUB%20id%3D%22lingo-sub-2756166%22%20slang%3D%22en-US%22%3ESUMIFS%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2756166%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20the%20following%20tables%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETable%201%20%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%221190%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2273%22%3EProject%20I%2FD%3C%2FTD%3E%3CTD%20width%3D%2282%22%3EName%3C%2FTD%3E%3CTD%20width%3D%22114%22%3EProject%20Criticality%3C%2FTD%3E%3CTD%20width%3D%22145%22%3EStart%20period%201%20(yyyyww)%3C%2FTD%3E%3CTD%20width%3D%22139%22%3EEnd%20period%201%20(yyyyww)%3C%2FTD%3E%3CTD%20width%3D%22174%22%3EHours%20work%2Fweek%20period%201%3C%2FTD%3E%3CTD%20width%3D%22139%22%3EStart%20period%202%20(yyyyww)%3C%2FTD%3E%3CTD%20width%3D%22150%22%3EEnd%20period%202%20(yyyyww)%3C%2FTD%3E%3CTD%20width%3D%22174%22%3EHours%20work%2Fweek%20period%202%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3EJohn%20Smith%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E202101%3C%2FTD%3E%3CTD%3E202143%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E202144%3C%2FTD%3E%3CTD%3E202203%3C%2FTD%3E%3CTD%3E7%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3EKaren%20Smith%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E202101%3C%2FTD%3E%3CTD%3E202202%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E202203%3C%2FTD%3E%3CTD%3E202304%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3EJohn%20Smith%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E202110%3C%2FTD%3E%3CTD%3E202113%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E202114%3C%2FTD%3E%3CTD%3E202119%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3EKaren%20Smith%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E202110%3C%2FTD%3E%3CTD%3E202202%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E202203%3C%2FTD%3E%3CTD%3E202245%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETable%202%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22120%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22120%22%3EPeriod%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2101%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2102%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2103%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2104%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2105%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2106%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2107%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2108%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2109%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2110%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2111%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2112%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2113%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2114%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2115%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2116%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2117%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2118%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2119%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2120%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2121%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2122%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2123%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2124%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2125%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2126%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2127%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2128%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2129%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2130%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2131%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2132%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2133%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2134%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2135%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2136%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2137%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2138%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2139%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2140%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2141%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2142%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2143%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2144%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2145%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2146%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2147%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2148%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2149%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2150%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2151%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2152%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2201%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2202%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2203%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2204%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2205%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2206%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E22..%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20table%202%20%2C%20I%20want%20to%20add%20new%20columns%20to%20calculate%20the%20SUM%20of%20working%20hours%20per%20period%20and%20per%20project%20criticality.%20I%20believe%20SUMIFS%20would%20be%20the%20formula%20to%20generate%20these%20new%20values.%2C%20but%20I%20have%20some%20trouble%20to%20do%20it...%26nbsp%3B%20Can%20you%20please%20help%20on%20the%20formula%20to%20use%20to%20generate%20these%20new%20columns%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22859%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22120%22%3EPeriod%3C%2FTD%3E%3CTD%20width%3D%22199%22%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3CEM%3E%3CSTRONG%3ESUM%20Hrs%20%2F%20Week%20C1%20John%20Smith%3C%2FSTRONG%3E%3C%2FEM%3E%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22199%22%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3CEM%3E%3CSTRONG%3ESUM%20Hrs%20%2F%20Week%20C2%20John%20Smith%3C%2FSTRONG%3E%3C%2FEM%3E%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22174%22%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3CEM%3E%3CSTRONG%3EHrs%20%2F%20Week%20C1%20Karen%20Smith%3C%2FSTRONG%3E%3C%2FEM%3E%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22167%22%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3CEM%3E%3CSTRONG%3EHrs%20%2F%20Week%20C2%20Karen%20Smith%3C%2FSTRONG%3E%3C%2FEM%3E%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2101%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2102%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2103%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22120%22%3E2104%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOR%20is%20there%20any%20other%20suggestiont%20to%20do%20this%20better%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20file%20is%20enclosed%20as%20well.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20Regards%2C%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2756166%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2757462%22%20slang%3D%22en-US%22%3ERe%3A%20SUMIFS%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2757462%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1157780%22%20target%3D%22_blank%22%3E%40Antoine102%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20B5%20on%20New%20Table%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUMIFS('Table%201'!%24F%242%3A%24F%245000%2C'Table%201'!%24B%242%3A%24B%245000%2CB%241%2C'Table%201'!%24C%242%3A%24C%245000%2CB%242%2C'Table%201'!%24D%242%3A%24D%245000%2C%22%26lt%3B%3D%22%26amp%3B200000%2B%24A5%2C'Table%201'!%24E%242%3A%24E%245000%2C%22%26gt%3B%3D%22%26amp%3B200000%2B%24A5)%2BSUMIFS('Table%201'!%24I%242%3A%24I%245000%2C'Table%201'!%24B%242%3A%24B%245000%2CB%241%2C'Table%201'!%24C%242%3A%24C%245000%2CB%242%2C'Table%201'!%24G%242%3A%24G%245000%2C%22%26lt%3B%3D%22%26amp%3B200000%2B%24A5%2C'Table%201'!%24H%242%3A%24H%245000%2C%22%26gt%3B%3D%22%26amp%3B200000%2B%24A5)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20to%20the%20right%20to%20E5%2C%20then%20fill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello,

 

I have the following tables:

 

Table 1 : 

 

Project I/DNameProject CriticalityStart period 1 (yyyyww)End period 1 (yyyyww)Hours work/week period 1Start period 2 (yyyyww)End period 2 (yyyyww)Hours work/week period 2
1John Smith120210120214352021442022037
2Karen Smith220210120220242022032023045
3John Smith220211020211322021142021193
4Karen Smith120211020220212022032022452

 

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?

 

PeriodSUM Hrs / Week C1 John SmithSUM Hrs / Week C2 John SmithHrs / Week C1 Karen SmithHrs / 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, 

3 Replies

@Antoine102 

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.


Hi Ijust realized there is a error on my file. The table 1 should be as follow:

[Project I/D ] [Name ] [ Project Criticality ] [Period 1 End date] [Hours period 1] [Period 2 End date] [Hours period 2 ] [Period 3 End date] [Hours period 3] [Period 4 End date] [Hours period 4]

Could I still use SUMPRODUCT? What formula would you recommend?

TKs,

@Antoine102 

So there is no start date anymore? See the attached version. I changed the layout of Table3 slightly.