asked on

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

SOLUTION

x-men

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

x-men

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

x-men

http://office.microsoft.com/en-us/excel-help/countifs-function-HA010047494.aspx
http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010342933.aspx?CTT=1

jppinto

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

garyrobbins

ASKER

x-men,
Could you elaborate and also show how to do the SUM formula?
Also, is this compatible with Excel 2003?
Thanks, Gary

ASKER CERTIFIED SOLUTION

jppinto

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Richard Daneke

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

garyrobbins

ASKER

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