Forum Discussion
SUMIFS Function Returning Value of First Occurrence Only Instead of Summing All
Thanks Riny_van_Eekelen , unfortunately, the data is too sensitive to attach a file.
However, I worked out what the problem was. My Dates had 'days added' to them. I thought they were whole integer days but they were derived from an average of days, which returned a decimal.
I was formatting this date as 'short date', which masked the fact that it was now a datetime. Hence the lookup failed unless it was a perfect intiger.
TimJackson If all these methods return the wrong sum but the COUNTIF on dates is correct as you mention, I suspect that the all except the first amounts for all dates are texts looking like numbers.
Do you get the correct grand total when you sum the entire range with amounts?
Riny_van_Eekelen thanks for your response. The dynamic array creates all the date rates in the same way at the same time.
Here is an Example date and forecasted amount:
You can see for the forecasted date of the 17th July the summed amount is £1489.01.
However if we look at a filter of the dynamic array you can see that there is no difference in the formatting (and it is indeed a proper formatted) date, but it is only picking the first value when summing, not all values that meet the criteria.
This happens, not just with this date specifically, but for all dates.
Thankyou in advance for looking at this issue.
TimJackson Sorry, but I can't do anything based on screenshots alone.
Riny_van_Eekelen I appreciate you trying to help. What do you need from me beyond this to help diagnose the problem?
TimJackson A file perhaps, demonstrating the formula not working. Attach it to your post or share a link to the file Onedrive (or similar) giving unrestricted access.