How to get total in day of week pattern

[sarfarazbutt]

I need help in below sheet, need to calculate Day of week pattern for example if this sheet A8 and H8 are Saturday and value of A9 and H9 is 10+10 so the answer in K9 will be 20. Anybody have idea plz

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
6											Total
7	1-Jan-11	2-Jan-11	3-Jan-11	4-Jan-11	5-Jan-11	6-Jan-11	7-Jan-11	8-Jan-11	9-Jan-11	10-Jan-11	Sat	Sun	Mon	Tue	Wed	Thu	Fri
8	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	2	2	2	1	1	1	1
9	10	20	10	10	10	10	10	10	10	10
10
11	1-Feb-11	2-Feb-11	3-Feb-11	4-Feb-11	5-Feb-11	6-Feb-11	7-Feb-11	8-Feb-11	9-Feb-11	10-Feb-11	Sat	Sun	Mon	Tue	Wed	Thu	Fri
12	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	1	1	1	2	2	2	1
13	10	10	10	10	10	10	10	10	10	10
14
15	1-Mar-11	2-Mar-11	3-Mar-11	4-Mar-11	5-Mar-11	6-Mar-11	7-Mar-11	8-Mar-11	9-Mar-11	10-Mar-11	Sat	Sun	Mon	Tue	Wed	Thu	Fri
16	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	1	1	1	2	2	2	1
17	10	10	10	10	10	10	10	10	10	10
Control

 

[AlphaFrog]

<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K9</th><td style="text-align:left">=SUMIF(<font color="Blue">$A8:$J8,7,$A9:$J9</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">L9</th><td style="text-align:left">=SUMIF(<font color="Blue">$A8:$J8,1,$A9:$J9</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M9</th><td style="text-align:left">=SUMIF(<font color="Blue">$A8:$J8,2,$A9:$J9</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">N9</th><td style="text-align:left">=SUMIF(<font color="Blue">$A8:$J8,3,$A9:$J9</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">O9</th><td style="text-align:left">=SUMIF(<font color="Blue">$A8:$J8,4,$A9:$J9</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">P9</th><td style="text-align:left">=SUMIF(<font color="Blue">$A8:$J8,5,$A9:$J9</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">Q9</th><td style="text-align:left">=SUMIF(<font color="Blue">$A8:$J8,6,$A9:$J9</font>)</td></tr></tbody></table></td></tr></table><br />

 

[sarfarazbutt]

Thanks for your idead but its not work in this sheet . This need to be fill in every cell of the sheet, and cannot copy paste fix formula i.e 1st Jan 2011 is Saturday but 1st Feb will not Saturday, need kind of if or lookup condition to solve the issue in whole sheet.

 

[AlphaFrog]

I do not understand why you can use COUNTIF in K8:Q8 but you cannot use SUMIF in K9:Q9

 

[sarfarazbutt]

Sorry i did not look that you add the number of day in formula, many thanks for your help. its clear

 

[AlphaFrog]

You are welcome.

One other idea is instead of having the weekday number in A8:J8, you can have the weekday as text. This text would be the same as found in K7:Q7. Then you could use K7:Q7 as a reference in both the COUNIF and SUMIF formulas.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
7	1-Jan-11	2-Jan-11	3-Jan-11	4-Jan-11	5-Jan-11	6-Jan-11	7-Jan-11	8-Jan-11	9-Jan-11	10-Jan-11	Sat	Sun	Mon	Tue	Wed	Thu	Fri
8	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	2	2	2	1	1	1	1
9	10	20	10	10	10	10	10	10	10	10	20	30	20	10	10	10	10
Sheet

 

[sarfarazbutt]

Many thanks. First one was worked perfect with me.

 

[sarfarazbutt]

This is working fine in one sheet which was 2012 year but when i do the same in 2011 year is not correct any comments / help.

look it AE100 (which is saturday) I input the 10 and reply suppose to be come in cell AN100 which is saturday but comes in An100 which is sunday

Excel Workbook
	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO
98	25.1.11	26.1.11	27.1.11	28.1.11	29.1.11	30.1.11	31.1.11	31	5	4	4	4	4	5	5
99	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Total	Mon	Tue	Wed	Thu	Fri	Sat	Sun
100	*	*	*	*	10	*	*	10	0	0	0	0	0	0	2
101	*	*	*	*	*	*	*	0	0	0	0	0	0	0	0
102	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Data_M1

 

[AlphaFrog]

Your formulas in AI100:AO100 are based on the numbered day of week in row 4.

Do your numbered days of the week in C4:AG4 match with the days of the week in C99:AG99 ?