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

# 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
1 Solution

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

Commented:
Assuming your dates are A1:A31 and numbers are B1:B31

=SUMPRODUCT(--((A1:A31<=D2)*(B1:B31)))
0

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

Finance 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

Author 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.