Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

SumIfs with Between Date Range

I have the following function =IFERROR(SUMIFS($L:$L,$D:$D,"*fj*b*",$F:$F,"*14",$E:$E,"<> hld",$C:$C,"mys")/COUNTIFS($D:$D,"*fj*b*",$F:$F,"*14",$E:$E,"<> hld",$C:$C,"mys"),0)

and need to add the additional criteria of between the date range of 2/2/14 and 5/3/14 and the date range is found in column K. What would be the best way to add the criteria?

Thanks
0
jmac001
Asked:
jmac001
  • 2
  • 2
1 Solution
 
Rgonzo1971Commented:
Hi,

you say you have a date range but only one column

How's that?

Regards
0
 
jmac001Author Commented:
Column K represent an opening date and my goal is to only add if the date falls in the first quarter which is Feb - May 3, is it possible to update the equation with only one date column?
0
 
Rgonzo1971Commented:
Hi,

pls try

=IFERROR(SUMIFS($L:$L,$D:$D,"*fj*b*",$F:$F,"*14",$E:$E,"<> hld",$C:$C,"mys",$K:$K,">="&DATE(2014,2,2),$K:$K,"<="&DATE(2014,5,3))/COUNTIFS($D:$D,"*fj*b*",$F:$F,"*14",$E:$E,"<> hld",$C:$C,"mys",$K:$K,">="&DATE(2014,2,2),$K:$K,"<="&DATE(2014,5,3)),0)

Open in new window

0
 
jmac001Author Commented:
Thanks that worked!
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now