Help with advanced dynamic ranges between start and end date

[Konstantine1]

G'day,

I hope I can articulate this one. Apologies, this is my first post.

I have a set of data within the range B3:K13 which includes random numbers under 10 using =RAND()*10.
I have created 4 named ranges (where A3 is blank):

Dates: A3:A13 (Numbered 1 - 10)
Dates1:A4:A13
Timesteps: B3:K3 (Dated 1/01/2016 - 10/01/2016)
Timesteps1: A3:K3

So I have 4 criteria:

Start Date:B25 (Dropdown List "Dates")
End Date: B26 (Dropdown List "Dates")
Start Timestep: B27 (Dropdown List "Timesteps")
End Timestep: B28 (Dropdown List "Timesteps")

There are two formulas I have used that can generate the answers I need.

=SUM(OFFSET($A$3,MATCH($B$25,Dates,0),MATCH($B$27,Timesteps,0),MATCH($B$26,Dates1,0)-MATCH($B$25,Dates,0),MATCH($B$28,Timesteps1,0)-MATCH($B$27,Timesteps,0)))

=SUM(INDEX($B$4:$K$13,MATCH($B$25,Dates,0),MATCH($B$27,Timesteps,0)):INDEX($B$4:$K$13,MATCH($B$26,Dates,0),MATCH($B$28,Timesteps,0)))

Everything is good so far however I want add another criteria to this formula.

I want to create a new formula that will still sum the dynamic ranges not only for the selected range (All week) but for weekends and weekdays as well within that selected range.

I tried this formula but it didn't work.

=SUM(IF($N$4:$N$13>=6,OFFSET($A$3,MATCH($B$25,Dates,0),MATCH($B$27,Timesteps,0),MATCH($B$26,Dates1,0)-MATCH($B$25,Dates,0),MATCH($B$28,Timesteps1,0)-MATCH($B$27,Timesteps,0)),0))

I have created a work around in order to get there =SUMIFS(P4:P13,Dates,">="&$B$25,Dates,"<="&$B$26). But I need it in one formula!

I know this is a tricky one but if anyone could help me out it would be much appreciated.

I got the idea from Excelisfun's EMT 1071 - https://www.youtube.com/watch?v=lh4VjEoD5WY

Here is a screenshot of my spreadsheet - https://postimg.org/image/c455h66in/

Cheers!

[FDibbins]

Hi, welcome to the forum

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[AliGW]

Welcome to the forum!

Unfortunately, your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site). Adding these links is not optional.

[Konstantine1]

Thanks for the great advice FDibbins.

[Konstantine1]

Hi AliGW,

My plan was to respond to the other forums if I received a solution. Of course I do not want to waste people's time. I appreciate any help I can get with this problem.

I read the link you provided and it makes sense. I posted on the MrExcel forum and have not gotten a response. That is why I have posted my problem here because I asking the help of more experts.

Here is my post on MrExcel (Excuse the repeat posts, I made a few errors) - https://www.mrexcel.com/forum/excel-...-end-date.html

[Konstantine1]

I figured out the solution. Thanks for anyone who looked it anyway.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[FDibbins]

So, what was your solution? Care to share?

[Konstantine1]

Sure thing FDibbins.

I replaced the below formula:

=SUM(IF($N$4:$N$13>=6,OFFSET($A$3,MATCH($B$25,Dates,0),MATCH($B$27,Timesteps,0),MATCH($B$26,Dates1,0)-MATCH($B$25,Dates,0),MATCH($B$28,Timesteps1,0)-MATCH($B$27,Timesteps,0)),0))

With this one:

=SUM(IF(OFFSET(A3,MATCH($B$25,Dates1,0)-1,13,MATCH($B$26,Dates1,0) MATCH($B$25,Dates,0),)>=6,INDEX($B$4:$K$13,MATCH($B$25,Dates,0),MATCH($B$27,Timesteps,0)):INDEX($B$4:$K$13,MATCH($B$26,Dates,0),MATCH($B$28,Timesteps,0))))

I used the offset and match functions to make my lookup array dynamic and equal the same amount of answers as my dynamic array formula "". Which gave answers - {7.34,8.022,9.761,2.417,7.84,2.366,9.744;8.704,5.259,4.221,4.35,5.584,6.362,4.468;4.43,3.924,2.451,5.516,3.475,4.276,2.327;2.582,2.685,6.182,1.523,3.28,8.94,7.296;3.083,1.948,6.161,8.737,7.752,4.814,4.708;0.329,9.719,7.613,2.891,4.172,2.225,8.112;5.899,3.524,8.756,8.571,3.837,9.323,5.897} You can see that each row has seven different values.

The problem I was having was that the original formula was that "$N$4:$N$13>=6" was generating "{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE}". This is a total of ten answers.

These formulas require the same amount of row height to generate an answer.