A little complicated range selection

[dembe]

Is it possible to create a formula in Excel that takes a certain sequence in the range that is repeated but according to the exactly selected sequence.
I have a range of 30 days per month from A1 to U1. Cells are filled with days that start randomly, eg: Wed, Thu, Fri, Sat, Sun, Mon, Tue, Wed, Thu, Fri, Sat, Sun, Mon, Tue, and so in 30 days.
1. Below in A21 to U2 I have values that I want, for only example, to SUM, but according to the criterion that only those cells from the top row that are in the range from Mon to Sun, that means the whole week, so it skips if the row starts from Wed, or end in middle day of week, but it searches for the whole week .
2. When it finds the first whole sequence Mon to Sun, it calculates it, then it searches for the next sequence Mon to Sun, calculates that and then the next sequence if there is one.

I know it sounds a bit complicated, but that's why I can't figure it out myself.

Example in attachment

[kvsrinivasamurthy]

All are ARRAY functions.
First. In B12

Please Login or Register  to view this content.

SECOND. In I12

Please Login or Register  to view this content.

THIRD. In P12

Please Login or Register  to view this content.

To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.

[XLent]

the sample is a little confusing and you infer you have multiple rows to aggregate, 30 days (but only 21 columns) but, perhaps something along the lines of:

Please Login or Register  to view this content.

the above would aggregate the numbers associated with "complete" weeks, in this example that would be columns C:P inclusive.
refer A17 in sample -- uploaded to account for {potential} locale differences re: delimiters

edit: typo in above, corrected (and updated attachment) @ 06:46 UK

[dembe]

Thanks guys.
kvsrinivasamurthy your is kind of .
XLent your is close what i want.

But my mistake that i didn't be precise.
Here is example of formula i made by hand and what i need.

keep in mind that Excel is in Croatian and the time format is 24 hours, but that shouldn't be a problem

[XLent]

so, still assuming the start day will vary (from one month to the next) see if the attached [N6] works for you (returns 8 using row 2)

this now assumes you're calculating Overtime (or equivalent) row by row

Please Login or Register  to view this content.

however, this does feel like a "sledgehammer" approach; you might be better off just storing wk 1-4 totals in separate columns, based on first Mon (such that sometimes wk 4 is 0), as this will be more intuitive

[dembe]

so, still assuming the start day will vary (from one month to the next) see if the attached [N6] works for you (returns 8 using row 2)

this now assumes you're calculating Overtime (or equivalent) row by row

Please Login or Register  to view this content.

however, this does feel like a "sledgehammer" approach; you might be better off just storing wk 1-4 totals in separate columns, based on first Mon (such that sometimes wk 4 is 0), as this will be more intuitive

Problem is with 10 or more people in schedules in whole year. Like i said in example i can do by hand but i just want to see is there way if i change days for all month that it will do automatically.

[kvsrinivasamurthy]

Pl see file. All formulas are in file.

[XLent]

Like i said in example i can do by hand but i just want to see is there way if i change days for all month that it will do automatically.

well, as mentioned, I'd suggest taking a look at the previously provided suggestion(s) to see if they meet your requirements. However, I'd simply add that you need to be careful of adopting elegance over practicality, particularly when simple approaches are likely to be quick and easy(ier) to maintain.

[kvsrinivasamurthy]

Some months there may be 4 weeks. Formulas are given for 4 weeks. Pl see file.
In F11 copied up to I12

Please Login or Register  to view this content.

NOTE: KEEP ALWAYS AE2 BLANK

[dembe]

Some months there may be 4 weeks. Formulas are given for 4 weeks. Pl see file.
In F11 copied up to I12

Please Login or Register  to view this content.

NOTE: KEEP ALWAYS AE2 BLANK

Exactly, thanks a lot for the help, I'll try that and put it in my table.

[kvsrinivasamurthy]

Pl mark the thread solved.