[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

How to count and sum based on date criteria?

Hello experts:

See attached example.  How do I count and sum my data based on date range criteria?

Thanks, Gary
Training-History-Log-EE.xls
0
garyrobbins
Asked:
garyrobbins
  • 3
  • 2
  • 2
  • +1
3 Solutions
 
x-menIT super heroCommented:
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
0
 
x-menIT super heroCommented:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
jppintoCommented:
Please take a look at the attached file. I've use this to count:

=COUNTIFS(H2343:H2357,">="&G2358,H2343:H2357,"<"&H2358)

and this to sum:

=SUMPRODUCT((H2343:H2357>=G2358)*(H2343:H2357<H2358)*(G2343:G2357))

between the 2 dates specified on cells G2358 and H2358.

jppinto
Training-History-Log-EE.xls
0
 
garyrobbinsAuthor Commented:
x-men,
Could you elaborate and also show how to do the SUM formula?  
Also, is this compatible with Excel 2003?
Thanks, Gary
0
 
jppintoCommented:
To count, use this instead:

=SUMPRODUCT((H2343:H2357>=G2358)*(H2343:H2357<H2358))

This is 2003 compatible.

Please check the attached file...

jppinto
Training-History-Log-EE.xls
0
 
Richard DanekeCommented:
The SUMIFS and COUNTIFS was introduced in Excel 2007 and remain in Excel 2010.  This avoids the challenging SUMPRODUCT functions.
Remember, you may download a trial version from microsoft website.
0
 
garyrobbinsAuthor Commented:
Thanks everyone for the prompt and thurough replies.

x-men, I'm sorry I was not more clear that I was working in Excel 2003.
jppinto, SUMPRODUCT comes through again...

Hope you all find my allocation equitable.

Gary
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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