• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

count values based on date value

I have date values from Dec1 to Dec31 in one column and number values in the other column next to each date. How do I calculate sum of the number values from Dec 1 up to the date which value equals the date in cell D1?

example

12/1/11    30
12/2/11    20
12/3/11     5
12/4/11     40
etc.

If the date in cell D2 is 12/2, then my sum will be 50.
0
teaone
Asked:
teaone
1 Solution
 
barry houdiniCommented:
Here's one way for any excel version...

=SUMPRODUCT((A2:A10>=DATE(2011,12,1))*(A2:A10<=D2),B2:B10)

assuming dates in A2:A10 and amounts in B2:B10

In Excel 2007 and later SUMIFS can be used

SUMIFS(B2:B10,A2:A10,">="&DATE(2011,12,1),A2:A10,"<="&D2)

regards, barry
0
 
cyberkiwiCommented:
Assuming your dates are A1:A31 and numbers are B1:B31

=SUMPRODUCT(--((A1:A31<=D2)*(B1:B31)))
0
 
barry houdiniCommented:
.......In fact if you don't have any date values earlier than 1st Dec you can just sum for <=D2, e.g.

=SUMIF(A2:A10,"<="&D2,B2:B10)

barry
0
 
Rob HensonFinance AnalystCommented:
Adding from from barry's suggestion, if you use two cells to specify range of dates ie D1 = Start Date & D2 = End Date:

=SUMIF(A2:A10,"<="&D2,B2:B10)-SUMIF(A2:A10,"<"&D1,B2:B10)

Thanks
Rob H
0
 
teaoneAuthor Commented:
perfect! Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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