Using noncontiguous ranges with SUMIFS

[Triedtwice]

I have a calendar-type layout with dates from 27 Dec 20 to 01 Jan 21 on B39:V39, 17 Jan 21 to 06 Feb 21 on B41:V41, 07 Feb 21 to 27 Feb 21 on B43:V43, etc.
On the rows beneath the dates, I have values that I need to sum, based on pay periods. I have named the noncontiguous day range as Date_Matrix and the noncontiguous value range as Hours_Matrix.
is it possible to use a formula to sum values that corresponding to a particular date, based on a data range? I've tried the following:

=SUMIFS(Hours_Matrix,Date_Matrix,">=C6",Date_Matrix,"<=D6")
where C6 is 01 Jan 21 and D6 is U.

Unfortunately, I get a #Value error.

are the noncontiguous ranges preventing me from accomplishing this?

 

[Joe4]

I don't think you can use SUMIFS on non-contiuous ranges.
Can you post range B39:V43 for us to see?
We may be able to work around it, if we know what is in those other cells.

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.

Also, you have an issue with how you are writing your criteria. If C6 and D6 are cell references, they need to be outside the double-quotes, i.e.

">=" & C6

 

[Triedtwice]

Thanks for the quick reply! I've added the XL2BB add-in, and am attempting to past a "mini-worksheet" from the clipboard. Let me know if it didn't work.

example_1.xlsx
	E
13
Sheet1

 

[Joe4]

It didn't work (you should be able to see the exact same we see after posting it). Note my comment about the "Test Here" forum you can use in testing and working out the bugs before posting the image to this thread.

 

[Triedtwice]

Thanks for attempting to help me (or help me get help), but I've abandoned the noncontiguous ranges and set it up as continuous, which worked as expected. You were likely correct pointing out that SUMIFS probably doesn't work with noncontiguous.