SUMIFS not working with criteria given

[bootdat]

For some reason the formula is not giving the right value and I'd want to know why and how to fix it. It happens that, it doesn't add up the END column although the SUMIFS formula contains "<=".

POSv3-General.xlsm
	E	F	G
5	Start	End	Sum of Sales
6	Monday, 28 December 2020	Sunday, 3 January 2021	GH₵ -
7	Monday, 4 January 2021	Sunday, 10 January 2021	GH₵ -
8	Monday, 11 January 2021	Sunday, 17 January 2021	GH₵ -
9	Monday, 18 January 2021	Sunday, 24 January 2021	GH₵ 120.00
Report

Cell Formulas
Range	Formula
E6	E6	=DATE(YEAR(B8),MONTH(B8),DAY(B8)-WEEKDAY(B8,3))
F6:F9	F6	=E6+6
G6:G9	G6	=SUMIFS(Table5[Sum],Table5[Date],">="&E6,Table5[Date],"<="&F6)
E7:E9	E7	=E6+7

This is the Source data table:

POSv3-General.xlsm
	A	B	C	D	E	F	G	H
1	Invoice Number	PayMethod	Date	Qty	Item Description	Price	Sum	Cus Name
2	JAN2100014	MoMo	Sun 10 January 21	1	6 Inch Whipped Cream Cake [Rd]	200	200	Kabala
3	JAN2100015	Cash	Sun 17 January 21	1	Lunch Basket Platinum [10-12 Heads]	1000	1000	Ken
4	JAN2100015	Cash	Sun 17 January 21	1	8 Inch Whipped Cream Cake [Rd]	300	300	Ken
5	JAN2100016	Bank	Sun 17 January 21	20	Meat Bas	10	200	Mandem
6	JAN2100016	Bank	Sun 17 January 21	20	Sandwich Quarters	5	100	Mandem
7	JAN2100016	Bank	Sun 17 January 21	20	Apple Pie	5	100	Mandem
8	JAN2100016	Bank	Mon 18 January 21	20	Chips & Catted Nuts	6	120	Mandem
Invoice Data

 

[jasonb75]

A date is evaluated as midnight at the start of the day, your dates in column C of the source have times as well so anything that is after midnight on the end date doesn't meet the criteria.

You can either edit the source data to remove the times, or change the formula as below. Either way will work correctly. The formula below will work with or without times.

If you copy the table back to your sheet, note that I've changed the formula to an actual date in the first cell.

Book1
	E	F	G
5	Start	End	Sum of Sales
6	28/12/2020	03/01/2021	0
7	04/01/2021	10/01/2021	200
8	11/01/2021	17/01/2021	1700
9	18/01/2021	24/01/2021	120
Sheet5

Cell Formulas
Range	Formula
F6:F9	F6	=E6+6
G6:G9	G6	=SUMIFS(Table5[Sum],Table5[Date],">="&E6,Table5[Date],"<"&F6+1)
E7:E9	E7	=E6+7

 

[bootdat]

Oh yeah, right.
That adds up. That fixes the problem. I understand now. Thanks very much.

A date is evaluated as midnight at the start of the day, your dates in column C of the source have times as well so anything that is after midnight on the end date doesn't meet the criteria.

You can either edit the source data to remove the times, or change the formula as below. Either way will work correctly. The formula below will work with or without times.

If you copy the table back to your sheet, note that I've changed the formula to an actual date in the first cell.

Book1
	E	F	G
5	Start	End	Sum of Sales
6	28/12/2020	03/01/2021	0
7	04/01/2021	10/01/2021	200
8	11/01/2021	17/01/2021	1700
9	18/01/2021	24/01/2021	120
Sheet5

Cell Formulas
Range	Formula
F6:F9	F6	=E6+6
G6:G9	G6	=SUMIFS(Table5[Sum],Table5[Date],">="&E6,Table5[Date],"<"&F6+1)
E7:E9	E7	=E6+7