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)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.