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.

 

[Glenn Kennedy]

Try;

=SUMIFS(Data!C:C,Data!B:B,">="&I7,Data!B:B,"<="&L7)

 

[Fluff]

it returns only 303 out of the 335

Do the dates in col B also include time?
If so try

=SUMIFS(Data!C:C,Data!B:B,">="&I7,Data!B:B,"<"&L7+1)

 

[rexhvn]

Do the dates in col B also include time?
If so try

=SUMIFS(Data!C:C,Data!B:B,">="&I7,Data!B:B,"<"&L7+1)

Thank you Fluff, you're spot on . The date column included the time by default, adding +1 helped fix it .

Appreciate your help!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[rexhvn]

Glad we could help & thanks for the feedback.

Hi Fluff,

Unfortunately, i'm back to square one for some reason.

I've updated my data set, but it doesn't seem to be calculating correctly.

My formula:
=IFERROR(SUMIFS(Receipts!AG:AG,Receipts!V:V,">="&I26,Receipts!V:V,"<="&M26+1),"")

The 'actual' is pulling $124k, where the total sum between 22nd Jan and 26th Jan is actually $345k.

Receipts AG:AG = values from sumif for currency exchange.
Receipts V:V = Short dates no time (even if remove +1 from the formal, the data isn't changing).

Any idea where i'm going wrong here?

 

[Fluff]

It maybe that some of the dates are text rather than proper dates.

 

[rexhvn]

It maybe that some of the dates are text rather than proper dates.

I'm a bit perplexed at the moment, sorry Fluff.

I'm using the same format from my other formula which is working just fine. But for some reason, this isnt working.

Interestingly enough, when I filter, the dates appear different.

 

[rexhvn]

Never mind, all fixed. I text to columns it to Date and it fixed the problem.

 

[Fluff]

Glad it's sorted & thanks for letting us know.