SUMIFS between two dates not working :S

[rexhvn]

Hi all,

I am hoping for some assistance, please. I think i'm losing my mind.

I'm working on a spreadsheet (forecast) where I am trying to calculate the amounts for each value between two dates from a drop down.

In this example, I want to return all the values between 01/01/2024 & 10/01/2024 (Australia Date Format)

Under forecast which shows a value of $408k in the image:
DATA!C:C = Date Range (Date + Expected Delay = Date)
DATA!B:B = Values
=SUMIFS(Data!C:C,Data!B:B,">=01/01/2024",Data!B:B,"<=10/01/2024")

The formula seems to work ok on this test, however, for some reason, even this formula isn't picking up all my values in my real spreadsheet.

For example, on my real spreadsheet using the example same formula (just with dates manually entered) it returns only 303 out of the 335 from the list where the date is equal to or greater than 01/01/2024 and equal to or lower than 10/01/2024. Any thoughts here?

I'm also having issues trying to adjust the formula to read from a cell drop down that contains the start and end date, as a opposed to manually writing them in the formula.
FROM DATE = I7
TO DATE = L7
=SUMIFS(Data!C:C,Data!B:B,">=I7",Data!B:B,"<=L7") - It just returns a zero value.

Any assistance would be appreciated.

 

[rexhvn]

Hi all,

Was just hoping to get some further assistance with this query. The original issue was my sumif formula was not calculating all the values within that specific date range (7 days), however adding +1 seem to have fixed it for this section.

I've built a new addition allowing the user to select any date range that will provide a sumif value based on that given date range. While adding +1 helped fixed the previous issue calculating the sum range between two specific date, i've noticed if I apply this to the new addition, it includes +1, thus the sumif is not quite matching.

Forumla: =IFERROR(SUMIFS('Open Invoices(Forecast)'!AZ:AZ,'Open Invoices(Forecast)'!AY:AY,">="&I262,'Open Invoices(Forecast)'!AY:AY,"<="&M262+1),"")

For Example: If i'm looking at data from 2nd April to 30th April removing the +1 in the formula, the sumif is showing $2.3m, however, if I highlight all the cells, i get a total of $2.6m. If I add +1, its then including data from the 1st of May.

I hope that makes sense. I'm trying to see if there is a solution to this problem. I can't have one source of data providing one figure, but look at the same date rate and give me another sum if total.

Any help is appreciated.

 

[Peter_SSs]

The formula suggested previously by @Fluff contained "<" not "<=" in the final condition. In your formula above you have "<="

Changing back to "<" may well solve your problem.

If not, to clarify in the absence of any sample data

1. Do I262 and M262 contain a date only, or date and time?
2. Do the values in column AY in 'Open Invoices(Forecast)' contain dates only, or date and time?