MrExcelPlayground.xlsm | ||||||
---|---|---|---|---|---|---|
B | C | D | E | |||
28 | 11/2/2020 | 50 | November-20 | 200 | ||
29 | 11/22/2020 | 100 | December-20 | 150 | ||
30 | 11/24/2020 | 50 | January-21 | 150 | ||
31 | 12/19/2020 | 100 | February-21 | 250 | ||
32 | 12/28/2020 | 50 | March-21 | 200 | ||
33 | 1/6/2021 | 100 | April-21 | 150 | ||
34 | 1/23/2021 | 50 | ||||
35 | 2/1/2021 | 100 | ||||
36 | 2/7/2021 | 50 | ||||
37 | 2/16/2021 | 100 | ||||
38 | 3/9/2021 | 50 | ||||
39 | 3/21/2021 | 100 | ||||
40 | 3/26/2021 | 50 | ||||
41 | 4/20/2021 | 100 | ||||
42 | 4/24/2021 | 50 | ||||
Sheet29 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E28:E33 | E28 | =SUMIFS($C$28:$C$42,$B$28:$B$42,">="&DATE(YEAR(D28),MONTH(D28),1),$B$28:$B$42,"<="&EOMONTH(D28,0)) |
Thank you James. Does it matter how the date format is in column B?
MrExcelPlayground.xlsm
B C D E 28 11/2/2020 50 November-20 200 29 11/22/2020 100 December-20 150 30 11/24/2020 50 January-21 150 31 12/19/2020 100 February-21 250 32 12/28/2020 50 March-21 200 33 1/6/2021 100 April-21 150 34 1/23/2021 50 35 2/1/2021 100 36 2/7/2021 50 37 2/16/2021 100 38 3/9/2021 50 39 3/21/2021 100 40 3/26/2021 50 41 4/20/2021 100 42 4/24/2021 50 Sheet29
Cell Formulas Range Formula E28:E33 E28 =SUMIFS($C$28:$C$42,$B$28:$B$42,">="&DATE(YEAR(D28),MONTH(D28),1),$B$28:$B$42,"<="&EOMONTH(D28,0))
Excel formulas do not care about the date format applied to cells. Formatting only affects the appearance, not the values, in the cells.Does it matter how the date format is in column B?
I also tried to do a COUNTIF with the same dataset and formulas. And it didn't work. Would you be able to assist?Thank you James. Does it matter how the date format is in column B?
If JamesCanale's SUMIFS formulas worked for your first question, you should be able to use the same details in COUNTIFS to do what you want. Just be aware that the arguments are in a little different order, as SUMIFS has one more argument than COUNTIFS.I also tried to do a COUNTIF with the same dataset and formulas. And it didn't work. Would you be able to assist?
That is helpful. Thank you!Excel formulas do not care about the date format applied to cells. Formatting only affects the appearance, not the values, in the cells.
Formulas only look at the values, and not the formats.
So as long as the entries are entered as dates, and not text, it shouldn't matter the date format that you choose.