Using noncontiguous ranges with SUMIFS

Triedtwice

New Member
Joined
Feb 17, 2014
Messages
31
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?
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,515
Office Version
  1. 365
Platform
  1. Windows
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.
Excel Formula:
">=" & C6
 
Upvote 0

Triedtwice

New Member
Joined
Feb 17, 2014
Messages
31
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
 
Upvote 0

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,515
Office Version
  1. 365
Platform
  1. Windows
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.
 
Upvote 0
Solution

Triedtwice

New Member
Joined
Feb 17, 2014
Messages
31
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. :(
 
Upvote 0

Forum statistics

Threads
1,186,440
Messages
5,957,843
Members
438,325
Latest member
fanofstuff

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top