Problem with SUMIFS formula

[Diomedas]

Hello Everyone,

I have a strange problem with SUMIFS formula. First formula works fine and returns 6603 value, but when i change it like second one, it always returns 0. I checked every column and cell's format's and they are all seem ok.
I need second formula because i'm going to expand it like this: " SUMIFS('Unavailable_Times'!$T:$T,'Unavailable_Times'!$U:$U,"<="&$B$4,'Unavailable_Times'!$U:$U,">="&$H$4)/7 "

1- SUMIFS('Unavailable_Times'!$T:$T,'Unavailable_Times'!$U:$U,$B$4)

2- SUMIFS('Unavailable_Times'!$T:$T,'Unavailable_Times'!$U:$U,">="&$B$4)

Note: B4 is 5/31/2022, T column includes numbers to sum, and U column includes short dates like 5/31/2022.

Thank you so much in advance.

 

[Joe4]

Welcome to the Board!

Are you sure that your value in cell B4 and in column U on your "Unavailable Times" sheets are entered as valid dates, and not entered as Text?
One easy way to check is to try changing the Date format on those cells, and see if the display values changes. If it does not, then they are Text, and your formula won't work correctly.
Be sure to check BOTH B4 and your data column U.

It might also be helpful if you could post a small sample of your data for us to see, so we can see exactly what the data you are working with looks like.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Diomedas]

Hi Joe, thanks for your quick reply! I'll try to upload my spreadsheet but it contains lots of data, i'm not sure this system allows me to upload it.

Anyway, i double checked my data and all date columns seem correct, there is no text there. I wonder if any limitation about sumifs, because when i change it from a spesific date to a date range it returns 0, that's why i begin to think my data exceed it's limitations.

 

[Joe4]

Hi Joe, thanks for your quick reply! I'll try to upload my spreadsheet but it contains lots of data, i'm not sure this system allows me to upload it.

Anyway, i double checked my data and all date columns seem correct, there is no text there. I wonder if any limitation about sumifs, because when i change it from a spesific date to a date range it returns 0, that's why i begin to think my data exceed it's limitations.

Nope, Excel has no issue with using SUMIFS in date ranges. See: How to Use SUMIFs between Two Dates using VBA in Microsoft Excel

If there is an issue, it is probably not being applied correctly, which is why I would like to see an example of your data.
If you use that XL2BB tool, you can post samples of your data for us to see.
Otherwise, you can post an image (though the tool is better, as it allows us to copy/paste your data to our workbooks).