Sum by Weak

[hardeep.kanwar]

Hi! Everybody

I have Workbook with 30 Sheets, 29 have Same Format ( Headers and Columns) Not Rows

And, Last one is Summary Sheet

In 29 Sheet we have Daily Report data

Excel Workbook
	A	B
189	28-Mar	* * * * * * * * * * * * * * * * -
190	29-Mar	* * * * * * * * * * * * * * * * -
191	30-Mar	* * * * * * * * * * * *-
192	31-Mar	* * * * * * * * * * * * * * * * -
193	TOTAL MAR	* * * * * * * * *668
194	1-Apr	* * * * * * * * * * * *-
195	2-Apr	* * * * * * * * * * * *-
Chandigarh

I have to Maintain Summary Sheet, DAILY, WEEKLY, MONTHLY

Its very Easy to Handle DAILY & MONTHLY

I am Facing the Problem in WEEKLY

What i Need in My SUMMARY SHEET, to Sum the Value by Week.

Excel Workbook
	B	J
6	AHMEDABAD	* * * * * * *-
7	AURANGABAD	* * * * * * *-
8	BANGALORE	* * * * * * 3
9	BHUBNESHWAR	* * * * * * *-
SUMMARY

Now, You have Seen this Week Start From 28th March to 2nd April ,11

In My Summary Sheet i have to Add Manually, data of 2 Cell i.e. 1st and 2nd April,

Is it Possible to Use only one Formula, which not Include the "TOTAL" of March Month

Regards

 

[venkat1926]

consider this sample from A1 to B22

<table x:str="" style="border-collapse: collapse; width: 96pt;" border="0" cellpadding="0" cellspacing="0" width="128"><col style="width: 48pt;" width="64" span="2"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt; width: 48pt;" x:num="40626" align="right" width="64" height="17">3/24/2011</td> <td style="width: 48pt;" x:num="" align="right" width="64">2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="40627" align="right" height="17">3/25/2011</td> <td x:num="" align="right">3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="40628" align="right" height="17">3/26/2011</td> <td x:num="" align="right">4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="40629" align="right" height="17">3/27/2011</td> <td x:num="" align="right">5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="40630" align="right" height="17">3/28/2011</td> <td x:num="" align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="40631" align="right" height="17">3/29/2011</td> <td x:num="" align="right">2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="40632" align="right" height="17">3/30/2011</td> <td x:num="" align="right">3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="40633" align="right" height="17">3/31/2011</td> <td x:num="" align="right">4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="40634" align="right" height="17">4/1/2011</td> <td x:num="" align="right">5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="40635" align="right" height="17">4/2/2011</td> <td x:num="" align="right">6</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="40636" align="right" height="17">4/3/2011</td> <td x:num="" align="right">7</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="40637" align="right" height="17">4/4/2011</td> <td x:num="" align="right">8</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="40638" align="right" height="17">4/5/2011</td> <td x:num="" align="right">9</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="40639" align="right" height="17">4/6/2011</td> <td x:num="" align="right">8</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="40640" align="right" height="17">4/7/2011</td> <td x:num="" align="right">7</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="40641" align="right" height="17">4/8/2011</td> <td x:num="" align="right">6</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="40642" align="right" height="17">4/9/2011</td> <td x:num="" align="right">5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="40643" align="right" height="17">4/10/2011</td> <td x:num="" align="right">4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="40644" align="right" height="17">4/11/2011</td> <td x:num="" align="right">3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="40645" align="right" height="17">4/12/2011</td> <td x:num="" align="right">2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="40646" align="right" height="17">4/13/2011</td> <td x:num="" align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="40647" align="right" height="17">4/14/2011</td> <td x:num="" align="right">2</td> </tr> </tbody></table>
copy these data in an empty sheet

now in C1 type this formula or copy

=WEEKDAY("3/28/11")
this will give you 2 which means Monday

in D1 type this formula or copy

=WEEKNUM(A1,$C$1)
copy D1 down the data
This gives the week number of the dates in columns A taking Monday as the week beginning


now in an empty cells copy this formula

=SUMPRODUCT((D1:D22=D5)*B1:B22)

I am sorry I have given little elaborate explanation so that you can understand my process of thought. the ultimate sumproduct formula can be tweaked without having formulas in C1 and column D.

I am sure you can do this and also use this for your data.