Sum of the Weekdays Values basing on weeknames on Weekends

[Venkatesh1414]

i have created a table from A1:L4.

In that A1:L1 is dates and A2:L2 is the names of Weeks for above dates in A1:L1

and in A3:L3 names of the data and A4:L4 values of A3:L3 data

now, here i want to formulate, whenever the "Saturday" arrived in A2:L2,the AHR value of that saturday has to Come from SUM of the remaining weekdays(monday to Friday) AHR value.

Same as for Even Sunday also has to get the AHR as sum of the remaining days including saturday

in this table i have considered the sunday is the last day of the Week.

attached the sheet with Table reference.

could anyone help me out from this at the earliest.

[unit285]

Your comment has officially confused me. Your sheet does not have a 'Monday' record.. What is AHR and THR ? Are you just needing a formula to add the AHR values of the remaining weekdays together under Saturday and again under Sunday? Do the Weekdays change columns?

[Venkatesh1414]

Thanks for your quick response!.

this is just a sample sheet, so there is no monday record in It. AHR and THR are the Names of the data in A4:L4 and, Yes i need the formula to add AHR values of the remaining days where is a Saturday in A2:L2 and yes the weekdays change columns.

it is little bit tricky, but i need your help on this.

[unit285]

Thanks for your quick response!.

this is just a sample sheet, so there is no monday record in It. AHR and THR are the Names of the data in A4:L4 and, Yes i need the formula to add AHR values of the remaining days where is a Saturday in A2:L2 and yes the weekdays change columns.

it is little bit tricky, but i need your help on this.

Do you require a Formula or can you use VBA ? VBA would be quite simple for this.

[Venkatesh1414]

i think it's better with formulae, because i want to execute it if the table extendeds for the remaining dates also it has to work.

[unit285]

i think it's better with formulae, because i want to execute it if the table extendeds for the remaining dates also it has to work.

This code should change dynamically if you add more date columns. Try it.

It needs to be put into the Sheet 1 object. Right Click Sheet 1, click View Code, and paste the code there.

Please Login or Register  to view this content.

Double Click on any cell in Sheet 1 to Trigger the macro.

If you prefer a Button to execute the code that can be easily done as well.

[samba_ravi]

i4=SUMIFS($A4:G4,$A$3:G$3,I$3,$A$1:G$1,">"&I$1-IF(I$2=7,6,IF(I$2=1,7,-999)))
j4=SUMIFS($B4:I4,$B$3:I$3,J$3,$A$1:H$1,">"&I$1-IF(I$2=7,6,IF(I$2=1,7,-999)))
The above and Copy I4:J4 and paste it in K4

[Venkatesh1414]

This code should change dynamically if you add more date columns. Try it.

It needs to be put into the Sheet 1 object. Right Click Sheet 1, click View Code, and paste the code there.

Please Login or Register  to view this content.

Double Click on any cell in Sheet 1 to Trigger the macro.

If you prefer a Button to execute the code that can be easily done as well.

Thanks its working, but it would be better if it calculated through formula

[unit285]

Did you try samba_ravi's formula? I'm not very good with complicated formulas. Sorry.

[Venkatesh1414]

i4=SUMIFS($A4:G4,$A$3:G$3,I$3,$A$1:G$1,">"&I$1-IF(I$2=7,6,IF(I$2=1,7,-999)))
j4=SUMIFS($B4:I4,$B$3:I$3,J$3,$A$1:H$1,">"&I$1-IF(I$2=7,6,IF(I$2=1,7,-999)))
The above and Copy I4:J4 and paste it in K4

i4=SUMIFS($A4:G4,$A$3:G$3,I$3,$A$1:G$1,">"&I$1-IF(I$2=7,6,IF(I$2=1,7,-999)))
j4=SUMIFS($B4:I4,$B$3:I$3,J$3,$A$1:H$1,">"&I$1-IF(I$2=7,6,IF(I$2=1,7,-999)))
The above and Copy I4:J4 and paste it in K4

Hi Samba _Ravi

Thanks for the assistance

yes, your formula is working, but I need some more hellp from you

Please sheet which i created, in this there are four Saturdays & Sundays totally we can apply your formula individually for each Week on Saturday and Sundays,

but if the places of the Saturdays and Sundays in A2:L2 were changed basing on A1:L1 dates the formula has To calculate automatically from the remaining days.

Example:

In the sheet i have given dates in A1:L1 is For January month and Week names in A2:L2 is as Per the above dates,

If i mentioned the February month dates from A1:L1 then places of the Week Name in A2:L2 were changed, so then AHR and THR values of Saturday and Sunday has Calculate Automatically from Sum of the remaining days of the Weeks.

Please try to help me on this and much appreciated your response

Please refer the attach complete sheet

[Venkatesh1414]

could anyone help me on the above please,it's urgent to me

[samba_ravi]

simply copy the formula from I4:J4 and paste it where ever you want (Saturday or Sunday)
see the attached file

in your attached file I was created one more sheet "Feb", change the dates, Copied the formula from I4:J4 of Jan sheet and pasted it in c4:F4 of "Feb" sheet it is working file