SUMIF a specific date falls within a month

[Tatum2020]

Hi,

I am trying to sum data where the date falls within a specific month in a year. Please see how my excel is structured:

Please help!

 

[JamesCanale]

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))

 

[Tatum2020]

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?

 

[Joe4]

Does it matter how the date format is in column B?

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.

 

[Tatum2020]

Thank you James. 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?

 

[Joe4]

I also tried to do a COUNTIF with the same dataset and formulas. And it didn't work. Would you be able to assist?

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.

See: MS Excel: How to use the COUNTIFS Function (WS)

 

[Tatum2020]

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.

That is helpful. Thank you!

 

[Joe4]

You are welcome.

 

[Tatum2020]

Hi,

Apologies me again (totally clueless when it comes to excel). I used the above formula to sum cells that are on the same row and I keep getting #VALUE error. Do I need to replace SUMIF with SUMPRODUCT? And how would that formula work?

Sorry for the silly questions!

 

[Joe4]

Please post the exact formula you are trying, and tell us what cell you are putting the formula in.