Forum Discussion

TimJackson's avatar
TimJackson
Copper Contributor
Nov 02, 2023
Solved

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

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!

excel
Formulas and Functions
  • TimJackson's avatar
    TimJackson
    Nov 02, 2023

    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. 

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Nov 02, 2023

    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?

    • TimJackson's avatar
      TimJackson
      Copper Contributor
      Nov 02, 2023

      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.

Resources