Nov 02 2023 04:03 AM
Nov 02 2023 04:03 AM
I am facing an unusual issue with the SUMIFS function in Excel. I am attempting to sum values from a column based on a specific date criterion. The formula is structured to sum values from a dynamic array on another sheet (Sheet6). Here is the formula I am using:
The second column of the dynamic array holds the expected pay amount, and the third column holds the expected pay date.
The formula should sum all the values from the dynamic array where the expected pay date matches the specified date.
The formula only returns the value of the first occurrence that meets the date criterion, instead of summing all the occurrences.
Verified data types and formatting for consistency.
Tested the formula with direct range references instead of using INDEX:
=SUMIFS(Sheet6!$C$3:$C$4000, Sheet6!$D$3:$D$4000, [@Date])
Tried using SUMPRODUCT as an alternative:
=SUMPRODUCT((Sheet6!$C$3:$C$4000) * (Sheet6!$D$3:$D$4000 = [@Date]))
Attempted an array formula using SUM and IF:
=SUM(IF(Sheet6!$D$3:$D$4000 = [@Date], Sheet6!$C$3:$C$4000, 0))
Utilized FILTER and SUM combination:
=SUM(FILTER(Sheet6!$C$3:$C$4000, Sheet6!$D$3:$D$4000 = [@Date]))
All these attempts resulted in either an incorrect sum (first occurrence only) or a #N/A error indicating "A value is not available to the formula or function."
I've also used COUNTIF to verify the number of occurrences of the specified date, and it returns the correct count, indicating multiple matching rows.
I'm at a loss as to why SUMIFS is not functioning as expected. Any insights or alternative solutions would be greatly appreciated!
Nov 02 2023 04:35 AM
@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?
Nov 02 2023 06:22 AM
@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.
Nov 02 2023 06:39 AM
@TimJackson Sorry, but I can't do anything based on screenshots alone.
Nov 02 2023 08:34 AM
@Riny_van_Eekelen I appreciate you trying to help. What do you need from me beyond this to help diagnose the problem?
Nov 02 2023 08:52 AM
@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.
Nov 02 2023 10:21 AMSolution
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.