PDA

View Full Version : Solved: Sum of sales in a Week



krishhi
02-06-2012, 01:01 AM
Hello There,

I am stuck with this. I want the sales of by weekly basis. I am looking for a formula to get the sum.

Here is a example.

http://s7.postimage.org/5emrigdob/Query.jpg

and Here is a sample file for your reference:

http://www.4shared.com/file/Ye_suVOK/query.html


Waiting for your reply,

Regards,
- Krrish

mohanvijay
02-06-2012, 02:56 AM
Try this custom function



Function Sum_Wk(D_Rng As Range, S_Rng As Range, Mon As Integer) As Double
Application.Volatile
Dim L_Rng As Range
Dim Res As Double
Dim Ct_R As Long
Ct_R = 0
For Each L_Rng In D_Rng
Ct_R = Ct_R + 1
If IsDate(L_Rng) Then
If DatePart("ww", L_Rng, vbMonday) = Mon Then
Res = Res + S_Rng.Cells(Ct_R).Value
End If
End If
Next
Set L_Rng = Nothing
Sum_Wk = Res

End Function



and enter formula like below
=sum_wk($A$2:$A$17,$B$2:$B$17,1) Returns 20
=sum_wk($A$2:$A$17,$B$2:$B$17,2) Returns 2521

Bob Phillips
02-06-2012, 03:27 AM
Try

=SUMPRODUCT(--($A$2:$A$17>=($A$2-WEEKDAY(A$2,2)+1)+((ROW(A1)-1)*7)),--($A$2:$A$17<($A$2-WEEKDAY($A$2,2)+1)+(ROW(A1)*7)),$B$2:$B$17)

krishhi
02-06-2012, 03:28 AM
Thank you so much mohanvijay (http://www.vbaexpress.com/forum/member.php?u=36127) and xld (http://www.vbaexpress.com/forum/member.php?u=2139)