do sum filter by date [closed]

问题:

In my business what I do is provide a book of 25 coupon. If customer buys anything (from special people say Person A, Person B, etc to whom I give coupon), Person A gives one coupon to the customer. What I am doing is maintaining database of the same in excel. First column have coupon code. Then Date on which I took details. Then Sold column where I put daily details of sold coupons. Then I have Remaining coupon column and finally the total coupon left from the respective coupon book

Data looks like below.

Coupon #    Date    Sold    Remaining   Starting Day Coupon Counter
Coupon A    22-Oct-12   22  3   25
Coupon B    22-Oct-12   16  9   25
Coupon C    22-Oct-12   24  1   25
Coupon A    23-Oct-12   3   0   3
Coupon B    23-Oct-12   9   0   9
Coupon C    23-Oct-12   1   0   1
Coupon D    23-Oct-12   5   20  25
Coupon E    23-Oct-12   8   17  25
Coupon F    23-Oct-12   2   23  25
Coupon D    24-Oct-12   20  0   20
Coupon E    24-Oct-12   17  0   17
Coupon F    24-Oct-12   23  0   23

What I want is same data for the respective date. Output I am looking is as below.

Date    Sold    Remaining   Starting Day Coupon Counter
22-Oct-12   62  13  75
23-Oct-12   28  60  88
24-Oct-12   60  0   60

Any idea to get this done? I know how to get sum, but not sure how to filter it by any stuff.

Edit 1

I know thiis can be done using Macro, however I want to use pre-defined function available in excel.

Any idea/ suggestions how to get this done?

回答1:

Pre-build your report sheet with dates, then use SUMIF like so:

=SUMIF(date range, date cell (date to get information for), entire column to sum for)

This uses french formulas but you should be able to make it work with your workbook knowing it is in fact SUMIF.