adding dependent upon date

[Legacy 15162]

I am trying to add up values that occured during a week. For example, all orders placed for the week ending May 16th (so, the May 10-16).

 

[Brian from Maui]

I am trying to add up values that occured during a week. For example, all orders placed for the week ending May 16th (so, the May 10-16).

Try something like,

=sumproduct((range>=a1)*(range<=a2),range to sum)

where a1 and a2 houses the dates

 

[Legacy 15162]

i actually went this route:

{=SUM(IF($A$3:$A$33<=(firstWeekEnd(Month)),(B$3:B$33+U$3:U$33)))}

 

[Aladin Akyurek]

i actually went this route:

{=SUM(IF($A$3:$A$33<=(firstWeekEnd(Month)),(B$3:B$33+U$3:U$33)))}

What does firstWeekEnd(Month) do?

 

[Legacy 15162]

Function firstWeekEnd(mnthSelected As String) As Date
firstWeekEnd = firstDay(mnthSelected)
dayOfWeek = Weekday(firstWeekEnd)
While dayOfWeek <> vbFriday
firstWeekEnd = firstWeekEnd + 1
dayOfWeek = Weekday(firstWeekEnd)
Wend

End Function

 

[Aladin Akyurek]

I wasn't asking for code...

If this udf computes a scalar (a date), how is it supposed to fit your question "I am trying to add up values that occured during a week. For example, all orders placed for the week ending May 16th (so, the May 10-16)."?

 

[Legacy 15162]

it's modified for those slightly for the other dates but it does the job.

{=SUM(IF($A$3:$A$33>$A40,IF($A$3:$A$33<=$A41,(B$3:B$33+U$3:U$33))))}

Btw, sorry about posting the code.

 

[Aladin Akyurek]

it's modified for those slightly for the other dates but it does the job.

{=SUM(IF($A$3:$A$33>$A40,IF($A$3:$A$33<=$A41,(B$3:B$33+U$3:U$33))))}

Btw, sorry about posting the code.

In case you're curious...

=SUMPRODUCT(($A$3:$A$33>$A40)*($A$3:$A$33<=$A41)*(B$3:B$33+U$3:U$33))

which is normally entered. Note that both formulas would not tolerate any text values like formula-blanks ("") in the ranges to sum.