Hi everyone, I am trying to use the SUMIFS formula to capture sums with 1) date criteria (i.e. all hours between Date 1 and Date 2) and specific names (i.e. upwards of 40 distinct names).
I have attached a worksheet sample below. For example, I would like to know the total number of hours for Adam Smith for the month of September. Note that the month period varies based on accounting month so I cannot use a typical September start and end date. See Columns M and N ==> I included the specific date periods that would be helpful.
I entered the formula used (see Adam Smith formula). I receive a #VALUE! error message. I tried the formula with a smaller set of names and manually entered the name (i.e. "Adam Smith") but with 40+ names and people coming in and going out, manually entering names cannot be best practice. Any help with this would greatly be appreciated.
Thanks everyone!!!!!
<colgroup><col><col><col><col><col><col><col><col><col span="2"><col span="2"><col><col span="2"></colgroup><tbody>
</tbody>
<colgroup><col><col><col><col span="3"><col><col span="2"><col></colgroup><tbody>
</tbody>
I have attached a worksheet sample below. For example, I would like to know the total number of hours for Adam Smith for the month of September. Note that the month period varies based on accounting month so I cannot use a typical September start and end date. See Columns M and N ==> I included the specific date periods that would be helpful.
I entered the formula used (see Adam Smith formula). I receive a #VALUE! error message. I tried the formula with a smaller set of names and manually entered the name (i.e. "Adam Smith") but with 40+ names and people coming in and going out, manually entering names cannot be best practice. Any help with this would greatly be appreciated.
Thanks everyone!!!!!
Columns | A | B | C | D | E | F | G | H | I | J | K | L | M | N |
1-Sep | 2-Sep | 3-Sep | 4-Sep | 5-Sep | 6-Sep | 2-Nov | 9-Nov | 10-Nov | Start | End | ||||
Adam Smith | 1 | 3 | 6 | 7 | 21 | 9 | 22 | 55 | 32 | September | 1-Sep | 25-Sep | ||
Brian Davis | 1 | 2 | 4 | 2 | 10 | 33 | 3 | 19 | October | 26-Sep | 30-Oct | |||
George Washington | 44 | 2 | 44 | 38 | 29 | 11 | 12 | 44 | 20 | November | 31-Oct | 27-Nov | ||
Target Answer | ||||||||||||||
September Total | November Total | September Total | October Total | November Total | ||||||||||
Adam Smith | =SUMIFS(B3:J5, A3:A5,A8,B2:J2,">="&M2,B2:J2,"<="&N2) | 47 | 0 | 109 | ||||||||||
Brian Davis | 19 | 0 | 55 | |||||||||||
George Washington | 168 | 0 | 76 | |||||||||||
<colgroup><col><col><col><col><col><col><col><col><col span="2"><col span="2"><col><col span="2"></colgroup><tbody>
</tbody>
<colgroup><col><col><col><col span="3"><col><col span="2"><col></colgroup><tbody>
</tbody>