SOLVED

SUMIFS Function Returning Value of First Occurrence Only Instead of Summing All

Copper Contributor

Hello,

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:

 

=SUMIFS(INDEX(Sheet6!$B$3#,,2),INDEX(Sheet6!$B$3#,,3),[@Date])

 

The second column of the dynamic array holds the expected pay amount, and the third column holds the expected pay date.

 

Expected Behavior:

The formula should sum all the values from the dynamic array where the expected pay date matches the specified date.

 

Actual Behavior:

The formula only returns the value of the first occurrence that meets the date criterion, instead of summing all the occurrences.

 

 

Steps Tried to Resolve:

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.

 

=COUNTIF(Sheet6!$D$3:$D$4000, [@Date])

 

I'm at a loss as to why SUMIFS is not functioning as expected. Any insights or alternative solutions would be greatly appreciated!

6 Replies

@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:

TimJackson_0-1698930946560.png

 

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.


TimJackson_1-1698931311622.png

 

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.

best response confirmed by TimJackson (Copper Contributor)
Solution

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. 

1 best response

Accepted Solutions
best response confirmed by TimJackson (Copper Contributor)
Solution

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. 

View solution in original post