SUMIF date range

  • Thread starter Thread starter Allan
  • Start date Start date
A

Allan

I am working on summing a list of measures per week.

How do I get add some numbers based on a date in a
separate column? I have tried the following forumula to
try and say "add all numbers who fall within the weekstart
date and the weekend date...

=SUMIF(Measures!A7:A701,"<=WeekGoals!A2 and >=WeekGoals!
B2",Measures!W7:W701)

Is there a better way?

Allan
 
A SumProduct formula for multi-conditional summing would do, using:

a) A multi-array SumProduct formula (Kosher)...

=SUMPRODUCT(--(Measures!$A$7:$A$701<=WeekGoals!A2),--(Measures!$A$7:$A$701>=
WeekGoals!B2),Measures!W7:W701)

=SUMPRODUCT((Measures!$A$7:$A$701<=WeekGoals!A2)+0,(Measures!$A$7:$A$701>=We
ekGoals!B2)+0,Measures!W7:W701)

b) A 2-array SumProduct formula....

=SUMPRODUCT((Measures!$A$7:$A$701<=WeekGoals!A2)*(Measures!$A$7:$A$701>=Week
Goals!B2),Measures!W7:W701)

c) A 1-array SumProduct formula...

=SUMPRODUCT((Measures!$A$7:$A$701<=WeekGoals!A2)*(Measures!$A$7:$A$701>=Week
Goals!B2)*Measures!W7:W701)
 
Back
Top