按日期过滤和 [英] do sum filter by date

问题描述

在我的生意中，我所做的是提供一张25张优惠券的书。如果客户购买任何东西（从特别的人员说A人，B人等给我的优惠券），A人给客户一个优惠券。我正在做的是维护excel中的数据库。第一列有优惠券代码。然后我收到细节的日期。然后卖出列，我把每日销售的优惠券的细节。然后我有剩余优惠券栏，最后从各自的优惠券书中剩下的总优惠券

数据如下所示。

 优惠券＃日期出售剩余开始日优惠券柜台
优惠券A 22-Oct-12 22 3 25 
优惠券B 22-Oct-12 16 9 25 
优惠券C 22-Oct-12 24 1 25 
优惠券A-23-Oct-12 3 0 3 
优惠券B 23-Oct-12 9 0 9 
优惠券C 23-Oct- 12 1 0 1 
优惠券D-12-Oct-12 5 20 25 
优惠券E-23-Oct-12 8 17 25 
优惠券F-12-Oct-12 2 23 25 
优惠券D 24-Oct-12 20 0 20 
优惠券E-12-Oct-12 17 0 17 
优惠券F-12-Oct-12 23 0 23 
  / pre> 
 
 
我想要的是相同数据的相同数据。输出我正在寻找如下。
 出售剩余开始日优惠券柜台
 22-Oct-12 62 13 75 
 23-Oct-12 28 60 88 
 24-Oct-12 60 0 60 
  
 
 $ b $任何想做这件事的想法？我知道如何获得金额，但不知道如何用任何东西过滤它。
 
 
 
编辑1 
 
 
 
我知道thiis可以使用宏完成，但是我想使用excel中可用的预定义函数。
 
 
 
任何想法/建议如何完成？
解决方案
使用日期预构建报表，然后使用SUMIF：
 
 
 
  = SUMIF（日期范围，日期单元格（获取信息的日期），整列列表） 
 
 
 
这使用法语公式，但您应该能够使其与您的工作簿一起使用，因为它实际上 SUMIF 。
 
 
 
  
 
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?
解决方案
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.



                        
这篇关于按日期过滤和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！