Sumif Formula Missing Values

[ksaint]

I have a sumif formula that is working for years 2019, 2017, 2016. The same sumif formula is not working for year 2018 River.
When I check the data tab and apply the same formula filters I am getting the correct value: 1,295,142. The sumif formula is incorrectly calculating 1,245,874.
I've checked datatypes and tried using sumproduct, yet I'm getting the same incorrect value with sumproduct. Any ideas on why this is occurring?

 

[Marc L]

As formulas never fail, you are confusing dates and your column P which contains dates & times !​

So try within your formula >= first day of the year and < to the first day of next year​

as 14 cells have a time the 12/31/18 like for example P982 which is equal to '12/31/2018 12:32:00'​

so superior to '12/31/18' (= 12/31/18 00:00:00) of your formula …​

​

According to your original formula, the logic is respected and the result is correct.​

Once any ♫ human ♪ has a correct logic, the formula returns the expected result …​

​

 

[ksaint]

Thanks so much Marc! I changed the date logic to use < 01/01/YYYY instead of <=12/31/YYYY and now it's including those missing values.
Saved me a lot of frustration!

 

[Marc L]

When a column contains dates & times, <= to a date is like < to the same date, the reason why with the next day it works …​

​