Solved: Sum of sales in a Week

[krishhi]

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.



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]

Try this custom function

[vba]

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

[/vba]

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]

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]

Thank you so much mohanvijay and xld