• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sumif Formula Missing Values

ksaint

New Member
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?
 

Attachments

  • Canada Sample 9.5.19.xlsx
    314.2 KB · Views: 5
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 …​
 
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!
 
When a column contains dates & times, <= to a date is like < to the same date, the reason why with the next day it works …​
 
Back
Top