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

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

# 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
• 3
• 2
• 2
• +1
3 Solutions

IT super heroCommented:
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
0

IT super heroCommented:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
0

IT super heroCommented:
0

Commented:
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

Author 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

Commented:

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

This is 2003 compatible.

jppinto
Training-History-Log-EE.xls
0

Commented:
The SUMIFS and COUNTIFS was introduced in Excel 2007 and remain in Excel 2010.  This avoids the challenging SUMPRODUCT functions.
0

Author 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

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