提示:其他語言是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 · 2 months 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.
      kellytte · 14 days ago
      Hi Ayesha,
      We can suppose the start is 1900/1/0, and add the two date periods to get the end date, and then get the difference between the start date and end date as attached image shown.
      Two formulas used:
      In Cell B7: =DATE(YEAR(0)+B4+B5,MONTH(0)+C4+C5,DAY(0)+D4+D5)
      In Cell B9: =DATEDIF(B1,B7,"Y")&" Years, "&DATEDIF(B1,B7,"YM")&" Months, "&DATEDIF(B1,B7,"MD")&" Days"
  • To post as a guest, your comment is unpublished.
    David · 5 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 · 8 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 · 7 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 · 8 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 · 11 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 · 11 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 · 1 years 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 · 11 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 · 2 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 · 2 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 · 2 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 · 2 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 · 4 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 · 5 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&"