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!

[shg]

Duplicate post: http://www.excelforum.com/showthread.php?t=1170669

Please don't do that.

[Konstantine1]

Hi shg, I am new user and wasn't sure which forum would be best to post in. Point noted.

[shg]

A five-minute read of the forum rules would help you stay squeaky-clean.