提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
马上登记
x

or

如何在Excel中的两个日期之间求和?

当工作表中有两个列表,如右图所示,一个是日期列表,另一个是值列表。 并且您只想总结两个日期范围内的值,例如,总结3 / 4 / 2014和5 / 10 / 2014之间的值,您如何快速计算它们? 现在,我介绍一个公式,让你在Excel中总结它们。


在Excel中使用公式计算两个日期之间的和值

幸运的是,有一个公式可以总结Excel中两个日期范围之间的值。

选择一个空白单元格并键入下面的公式,然后按 输入 按钮。 现在你会得到计算结果。 看截图:

=SUMIFS(B2:B8,A2:A8,">="&E2,A2:A8,"<="&E3)

注意:在上面的公式中,

  • D3:D22 是你要总结的价值表
  • B3:B22 是您将根据的总和的日期列表
  • G3 是具有开始日期的单元格
  • G4 是具有结束日期的单元格
注意丝带 公式太难记了? 将公式保存为自动文本条目,以便将来只需点击一下即可重复使用!
阅读全文... 免费试用

在每个会计年度,每半年或每周轻松汇总数据 在Excel中

Kutools for Excel提供的数据透视表特殊时间分组功能可以添加一个帮助器列以根据指定的日期列计算会计年度,半年度,周数或星期几,并让您轻松地计算,求和,或根据新的数据透视表中的计算结果得出的平均列。 全功能免费试用30天!

Kutools for Excel -包括多个用于Excel的300便捷工具。 全功能免费试用30天,无需信用卡! 现在加入


使用Excel中的过滤器对两个日期之间的值求和

如果需要对两个日期之间的值求和,并且日期范围经常更改,则可以为特定范围添加过滤器,然后使用SUBTOTAL函数在Excel中指定的日期范围之间求和。

1。 选择一个空白单元格,输入以下公式,然后按Enter键。

= SUBTOTAL(109,D3:D22)

注意:在上面的公式中,109表示总和过滤值,D3:D22表示您将总结的值列表。

2。 选择范围标题,然后单击添加过滤器 数据 > 筛选.

3。 单击日期列标题中的过滤器图标,然后选择 日期过滤器 > 之间。 在“自定义自动筛选”对话框中,根据需要键入开始日期和结束日期,然后单击 OK 按钮。 总值将根据过滤值自动更改。


相关文章:


Kutools for Excel解决了您的大多数问题,并使您的生产率提高了80%

  • 重用: 快速插入 复杂的公式,图表 以及你以前用过的任何东西; 加密单元格 密码; 创建邮件列表 并发送电子邮件...
  • 超级方程式酒吧 (轻松编辑多行文字和公式); 阅读布局 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不丢失数据; 分裂细胞含量; 组合重复的行/列...防止重复的细胞; 比较范围...
  • 选择复制或唯一 行; 选择空行 (所有细胞都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择......
  • 精确复制 多个单元格而不更改公式参考; 自动创建参考 多张表; 插入项目符号,复选框等等......
  • 提取文本,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级过滤器 (将过滤方案保存并应用到其他工作表); 高级排序 按月/周/日,频率等; 特殊过滤器 用粗体,斜体......
  • 结合工作簿和工作表; 根据键列合并表; 将数据拆分为多个表格; 批量转换xls,xlsx和PDF...
  • 超过300强大的功能。 支持Office / Excel 2007-2019和365。 支持所有语言。 在您的企业或组织中轻松部署。 全功能30天免费试用。
kte tab 201905

Office选项卡为Office提供选项卡式界面,使您的工作更轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,Publisher,Access,Visio和Project。
  • 在同一窗口的新选项卡中打开并创建多个文档,而不是在新窗口中。
  • 通过50%提高您的工作效率,每天为您减少数百次鼠标点击!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Ayesha · 14 days ago
    Hi,

    can you help me to calculate two working experiences in excel.

    Ex: 12 Yeas 3 Months 5 Days - CDB Finance company

    9 Years 4 Months 7 Days - Commercial Bank

    These are two working experiences in two working places of one person. how to sum these two experiences as a total experience in excel. Please help me.
  • To post as a guest, your comment is unpublished.
    David · 3 months ago
    Hi all,

    What if one would like to make a date range non-mandatory?

    Meaning, let's say that I have two cells with dates in them that are used as my beginning and end date threshold, but when those cells aren't populated, the sum for all dates is pulled from the data set.

    My formula (below) has two match criteria with the date ranges at the back of the formula. What I'm trying to do is only filter the sum by the date range if a condition is met (ideally a cell that acts as select Yes or No on "Do you want to limit the sum to a specific date range?"

    =SUMIFS(BURN_DATA!$S:$S,BURN_DATA!$R:$R,$A12,BURN_DATA!$H:$H,I$3,BURN_DATA!O:O,">="&I9,BURN_DATA!O:O,"<="&J9)


    THANKS IN ADVANCE!
  • To post as a guest, your comment is unpublished.
    David · 6 months ago
    Aloha, I'm trying to figure out how to sum another column of units, when one column of dates is sequential. Example: I have rows with dates, Mar 1, Mar 2, mar3, then marc 6,7, then march 10.
    I need to sum the corresponding units in of another column for mar 1-3, mar 6-7 and mar 10.
    • To post as a guest, your comment is unpublished.
      kellytte · 5 months ago
      Hi David,
      You can use methods introduced in this webpage to sum each period one by one.
      Or you can add a helper column before the date column as attached image shown, and then apply the Subtotal feature.
  • To post as a guest, your comment is unpublished.
    CAA · 6 months ago
    I am trying to create a holiday calendar to calculate the following information:

    Start Date:
    End Date (if there is one, otherwise calculate on the entire holiday year_
    No of Holidays
    Holiday year runs from 01/04 to 31/03 with an entitlement of 20 days plus bank holidays

    any help is much appreciated
  • To post as a guest, your comment is unpublished.
    Kasim · 9 months ago
    Hello there,
    I am a sales man at hotel and I want to create a rate plan for different dates and room types,
    for example
    10.08.19 rate 50 usd per person,
    11.08.19 rate 45 usd per person
    12.08.19 rate 55 usd per person
    13.08.19 rate 60 usd per person
    14.08.19 rate 60 usd per person

    when guest ask me 2 different room types for example family room =pp rate x 3
    and standard room =pp rate x 2

    check in 10.08 and for 5 nights.. On rate plan I filled all dates rates, but I cannot arrange for room types..

    could you help someone to me pls..
  • To post as a guest, your comment is unpublished.
    Steve · 9 months ago
    I'm trying to sum the monthly data, in column E, between the Date in Q4 and R4, using the dates in column A. All I get is 0 for an answer. Am I making a mistake or is there a bug in Excel?

    =SUMIFS('Major business figures'!$E:$E,'Major business figures'!$A:$A,">"&Analysis!Q$4,'Major business figures'!$A:$A,"<="&Analysis!R$4)
  • To post as a guest, your comment is unpublished.
    Yaroslav · 10 months ago
    Hello Excel gurus,

    quick question. I`m trying to sum amounts that are in date range but all i`m trying is useless. The task is the following:

    I have column with trade amounts (more then 1 hundred lines, means different trades). Each trade have booking date and Maturity date. First trade was done on 27.04.2017 and last one 21.11.2018. So, I need to sum trades amounts for each and every date separately starting from 27.04.2017 and till 21.11.2018. How to type the formula correctly having mentioned conditions on top?

    Appreciated in your help.

    Thansk
  • To post as a guest, your comment is unpublished.
    vanji · 1 years ago
    hai Friends I have one dout, how to calculate how many dates between one date to another date.
    • To post as a guest, your comment is unpublished.
      kellytte · 9 months ago
      Hi vanji,
      Just apply the formula =later date – earlier date
  • To post as a guest, your comment is unpublished.
    Hayden · 1 years ago
    does the date format matter?
    The formula when i type the 5th July as 5/7/2018, the formula is picking up 7th May instead.
  • To post as a guest, your comment is unpublished.
    Accountant · 1 years ago
    Hi there,


    I want to use a IF formula to pick up the number in a cell, only if the dates in which it rates to fall between two dates. Eg if the start date is between the two dates, then pick up a number from the cell.



    Help
    • To post as a guest, your comment is unpublished.
      kelly001 · 1 years ago
      Hi Accountant,
      For example:
      A1 is the first date 2018/2/27, A2 is the last date 2018/4/13, and you will see if the date in C1 falls in the date range. In this case, you can use this formula =IF(AND(C1>A1,C1
  • To post as a guest, your comment is unpublished.
    Bogdan Mateescu · 1 years ago
    Very helpful, thanks!
  • To post as a guest, your comment is unpublished.
    Darren · 1 years ago
    If I wanted to incorporate another IF into this formula how would I do it? My formula looks like this:

    =SUMIFS(E2:E51,C2:C51,">="&C52,C2:C51,"<="&AH52)
    I also want to include a variable in CELL D52 from the range of D2:D51.
    • To post as a guest, your comment is unpublished.
      IMRAN AHMAD · 1 years ago
      Can you do like this
      =SUMIFS(E2:E51,C2:C51,">="&C52,C2:C51,"<="&AH52,D2:D51,D52)
    • To post as a guest, your comment is unpublished.
      Tang Kelly · 1 years ago
      Hi Darren,
      If you are not familiar with the SUMIFS function, you can click Formulas > Insert Functions, type SUMIFS in the search for a function box, and click the Go button, and then specify as much criteria as you need in the Function Arguments dialog box. See screenshot:
  • To post as a guest, your comment is unpublished.
    Sharon · 1 years ago
    =SUMIFS(Expenses!D6:K300,Expenses!C6:C300,"> ="&References!B5,Expenses!C6:C300,"< ="&References!C5)

    returns #VALUE. Where am I going wrong? Expense tab, column C has dates. Expenses tab columns D thru K have the $$ amounts that need to be summed. References tab B5 has beginning date. References tab C5 has ending date.
    • To post as a guest, your comment is unpublished.
      Tang Kelly · 1 years ago
      Hi Sharon,
      There are two things you need to pay attention:
      (1) Remove space between > and =
      (2) Check if the dates are not stored as text. If stored as text, convert them to date/number.
  • To post as a guest, your comment is unpublished.
    hardik · 2 years ago
    DATED wgt
    09-05-2017 0.71
    30-05-2017 0.55
    07-06-2017 0.53
    07-06-2017 0.50
    07-06-2017 0.36
    07-06-2017 1.01
    08-06-2017 0.80
    13-06-2017 0.51
    13-06-2017 0.50
    13-06-2017 1.02
    14-06-2017 3.01
    15-06-2017 1.00
    15-06-2017 0.71
    19-06-2017 0.54
    24-06-2017 1.40
    26-06-2017 1.61
    27-06-2017 2.53
    30-06-2017 0.70
    04-07-2017 0.30
    04-07-2017 0.30
    04-07-2017 1.30
    04-07-2017 0.50
    05-07-2017 0.61
    10-07-2017 1.01
    10-07-2017 0.60
    10-07-2017 0.70
    12-07-2017 0.50

    i want to calculate date wise weight and convert to month



    Total month
    May -
    June -
    July -
  • To post as a guest, your comment is unpublished.
    Deepak Nandwanshi · 2 years ago
    thanks bro it's working
  • To post as a guest, your comment is unpublished.
    CPY · 2 years ago
    No idea why the formula is not working :( I am using it exactly. When I copy the sample data it works but, when I put in my data it fails. I never had an issue with sumifs ...weird Here is my formula....=SUMIFS('KPI Data'!$G:$G,'KPI Data'!$E:$E,">="&$C$4,'KPI Data'!$E:$E,"
    • To post as a guest, your comment is unpublished.
      Tariq · 2 years ago
      you need to change ">=" to "> =". The code they provided is missing the space.
  • To post as a guest, your comment is unpublished.
    Faheem · 2 years ago
    Thanks a bunch. Works perfectly well.
  • To post as a guest, your comment is unpublished.
    Pavan · 2 years ago
    I want the formula for exactly reverse criteria.. I want to arrive a value of a date.. I have from and to dates and value..
  • To post as a guest, your comment is unpublished.
    tabish misbah · 3 years ago
    hi i want to apply this formula on other sheet while my values and date are on other sheet how could this be possible please
  • To post as a guest, your comment is unpublished.
    Chantal · 3 years ago
    Hi,

    I require a formula which is kind of the reverse of the =SUMIFS(B2:B8,A 2:A8,">="&E2,A2 :A8,"
  • To post as a guest, your comment is unpublished.
    Chris · 3 years ago
    Hi,

    I need a formula that will display a total if the start and finish dates are between dates shown in a row.

    I have tried various IF( AND...) formulas but get caught out on either the start or finish dates
  • To post as a guest, your comment is unpublished.
    Robert · 3 years ago
    Good day,

    Info to note
    * working with 10 products (Prices vary every day) prices in worksheet 1-"Prices" (Colum A dates) (Products Row 2C-M) and quantity sold for the day in sheet 2 "Book 34" (each book consist of 100 invoices (1-100 in row B dates in A) products in row 2 (C-M) as well.
    * should I add a product the formula needs to identify with a formula not with an amount

    Example. I need to type the date and the quantity in each product and the total formula will identify with the product and date and multiply together.

    Please assits
  • To post as a guest, your comment is unpublished.
    Fred · 3 years ago
    I am successful using the above formula ( =SUMIFS(B2:B8,A2:A8,">="&E2,A2:A8,"
  • To post as a guest, your comment is unpublished.
    ara · 3 years ago
    Need formula for subtotal of "A" within date range. (total $ collected from "A" between 4/1 and 4/6, total $ collected from "B" between 4/1 and 4/16)
  • To post as a guest, your comment is unpublished.
    Samantha P · 3 years ago
    I have used your formula, but it only works if you type in the date, it is very static. If I make it look at a cell, it doesn't sum. What am I doing wrong.=SUMIFS(B2:B8,A2:A8,">=e4
    ",A2:A8,"
    • To post as a guest, your comment is unpublished.
      Hutchwa · 3 years ago
      I was having same issue but found another website that showed the answer:we write =SUMIFS($C$5:$C$95,$B$5:$B$95,">="&$F$5,$B$5:$B$95,"
  • To post as a guest, your comment is unpublished.
    Anon... · 3 years ago
    If you need a reference to another book/worksheet, use something like CONCATENATE(">=",'GLOBAL REPORT'!E4) as the criteria.Forsome reason this is needed.
  • To post as a guest, your comment is unpublished.
    Nadine · 3 years ago
    Which formula would you use should you wish to add certain cell values should the date be within 10 days of the current date of the spreadsheet. I.E I have two date cells, 1 being an automatic date updater when spreadsheet is opened and another date cell being that of which when an entry was made. There is over 100 lines of this and would like to add just the sales within 10 days of current date.
  • To post as a guest, your comment is unpublished.
    amcbreen · 3 years ago
    i have worksheet 1 with customer list and date of order.
    On second work sheet i have a weekly sales report with start and end range.

    i would like to look up date of order and summarize sales by week if the date range falls with the start and and end range on workbook 2. I've tried combining a sumifs, vlookup with a statement and can not resolve the calculation errors.
  • To post as a guest, your comment is unpublished.
    xlq · 4 years ago
    How do you do this when the date range is defined in another column?

    E.g. I have a main worksheet with a "week ending" column and another with expenses entered on a daily basis. How can I find and sum all expenses on or up to 7 days before each week ending date?

    =SUMIF(expenses!A2:expenses!A1024,main!$A$2&"