SUMIFS not working with criteria given

bootdat

New Member
Joined
Nov 22, 2018
Messages
44
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 "<=".

Cell Formulas
RangeFormula
E6E6=DATE(YEAR(B8),MONTH(B8),DAY(B8)-WEEKDAY(B8,3))
F6:F9F6=E6+6
G6:G9G6=SUMIFS(Table5[Sum],Table5[Date],">="&E6,Table5[Date],"<="&F6)
E7:E9E7=E6+7



This is the Source data table:

POSv3-General.xlsm
ABCDEFGH
1Invoice NumberPayMethodDateQtyItem DescriptionPriceSumCus Name
2JAN2100014MoMoSun 10 January 2116 Inch Whipped Cream Cake [Rd]200200Kabala
3JAN2100015CashSun 17 January 211Lunch Basket Platinum [10-12 Heads]10001000Ken
4JAN2100015CashSun 17 January 2118 Inch Whipped Cream Cake [Rd]300300Ken
5JAN2100016BankSun 17 January 2120Meat Bas10200Mandem
6JAN2100016BankSun 17 January 2120Sandwich Quarters5100Mandem
7JAN2100016BankSun 17 January 2120Apple Pie5100Mandem
8JAN2100016BankMon 18 January 2120Chips & Catted Nuts6120Mandem
Invoice Data
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,654
Office Version
  1. 365
Platform
  1. Windows
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.
Cell Formulas
RangeFormula
F6:F9F6=E6+6
G6:G9G6=SUMIFS(Table5[Sum],Table5[Date],">="&E6,Table5[Date],"<"&F6+1)
E7:E9E7=E6+7
 
Solution

bootdat

New Member
Joined
Nov 22, 2018
Messages
44
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.
Cell Formulas
RangeFormula
F6:F9F6=E6+6
G6:G9G6=SUMIFS(Table5[Sum],Table5[Date],">="&E6,Table5[Date],"<"&F6+1)
E7:E9E7=E6+7
 

Watch MrExcel Video

Forum statistics

Threads
1,130,046
Messages
5,639,761
Members
417,109
Latest member
996

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top