跳到主要内容

如何在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 - 使用 300 多种基本工具增强 Excel 功能。 享受全功能 30 天免费试用，无需信用卡！ 立即行动吧!

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

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

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

=小计（109，D3：D22）

注意：在上式中，109表示过滤后的值之和，D3：D22表示要汇总的值列表。

2。 选择范围标题，然后通过单击添加过滤器 时间 > 筛选器.

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

相关文章：

 
 

根据Excel中另一列中的条件求和的值
使用公式，数据透视表或出色的工具可以根据Excel中另一列中的条件轻松汇总值。

 
 

汇总除Excel中标题以外的整个列
2种方式（公式和VBA）对整个列求和，只有Excel中的列标题除外。

 
 

在Excel中按工作日/月/季度/年汇总数据
与用公式对每个月总计的繁琐求和进行比较，本文介绍了一些技巧，可以轻松地在Excel中按工作日，月份，季度或年份批量汇总数据。

 
 

在Excel中基于月和年的总和值
使用公式或第三方工具在Excel中根据月和年快速求和值。

最佳办公生产力工具

🤖 Kutools 人工智能助手：基于以下内容彻底改变数据分析： 智能执行   |  生成代码  |  创建自定义公式  |  分析数据并生成图表  |  调用 Kutools 函数...
热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup  |   多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ....
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ....
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧   |  工作簿和工作表管理器   |  资源库 （自动文本）   |  日期选择器   |  合并工作表   |  加密/解密单元格   |  按列表发送电子邮件   |  超级筛选   |   特殊过滤器 （过滤粗体/斜体/删除线...）...
前 15 个工具集:  12 文本 工具 (添加文本, 删除字符，...）   |   50+ 图表 类型 (甘特图，...）   |   40+ 实用 公式 (根据生日计算年龄，...）   |   19 插入 工具 (插入二维码, 从路径插入图片，...）   |   12 转化 工具 (小写金额转大写, 货币兑换，...）   |   7 合并与拆分 工具 (高级组合行, 分裂细胞，...）   |   ... 和更多

使用 Kutools for Excel 增强您的 Excel 技能，体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。  单击此处获取您最需要的功能...

产品描述

Office Tab 为 Office 带来选项卡式界面，让您的工作更加轻松

  • 在Word，Excel，PowerPoint中启用选项卡式编辑和阅读，发布者，Access，Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50％，并减少数百次鼠标单击！
Comments (48)
No ratings yet. Be the first to rate!
SANJAY VAGHELA
SANJAY VAGHELA
This comment was minimized by the moderator on the site
INVOICE DATE TOTAL
01-09-2021 19817
02-09-2021 44791
03-09-2021 26780
03-09-2021 58500
03-09-2021 2029
04-09-2021 79260
04-09-2021 2120
04-09-2021 9187
04-09-2021 5698
04-09-2021 1400
04-09-2021 1900
04-09-2021 7500
04-09-2021 830

i have above data in one sheet and i need date wise total value as below in another sheet,01-09-2021 19817
02-09-2021 44791
03-09-2021 87309
04-09-2021 107895

pls give formula details for itsanjay
Reply
0
0
kellytte
kellytte
This comment was minimized by the moderator on the site
Hi Jett,
You can use the SUMIFS function to sum by multiple criteria simultaneously.
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2,…, criteria_rangeN, criteriaN)
Reply
Report
0
0
Jett
Jett
This comment was minimized by the moderator on the site
What if there are multiple columns to sum up like one for interest, one for penalty, and one for principal amount? and it still need to be between two dates
Reply
0
0
gerardy
gerardy
This comment was minimized by the moderator on the site
hi i have a question related to your topic,if the case is like thisDATE AMOUNT17 SEP 70.000 60.00018 SEP 30.00019 SEP 20.000 10.000IF i use sum if between date 17 sept to 19 sept the 60.000 & 10.000 can't be include in the total.what formulas should i use to include the 60.000 and 10.000 in the total?
Reply
0
0
Ejaz
Ejaz
This comment was minimized by the moderator on the site
Hi, i have a question related to sumifs:

want to calculate a sum between two dates e.g. start of date and end of date, but at times there are payments which were made in a new month but connected to the expense month, For example if a payment date is Oct 5th but the payment is for September and I would like to show this payment for the month of September, the formula should ignore the Oct 5th date and consider the payment in September. How do i do that
Reply
0
0
John Morrissey
John Morrissey
This comment was minimized by the moderator on the site
Help! I can only get this formula to work if I convert to a MM/DD/YYYY format but all of my data is DD/MM/YYYY. The cells are formatted correctly but the formula doesn't work unless I change from eg. 14/01/2020 to 01/14/2020.
Reply
0
0
Ayesha
Ayesha
This comment was minimized by the moderator on the site
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.
Reply
0
0
kellytte
kellytte     Ayesha
This comment was minimized by the moderator on the site
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"
View Attachments (1 / 5)
Reply
Report
0
0
David
David
This comment was minimized by the moderator on the site
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!
Reply
0
0
David
David
This comment was minimized by the moderator on the site
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.
Reply
0
0
kellytte
kellytte     David
This comment was minimized by the moderator on the site
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.
View Attachments (1 / 5)
Reply
Report
0
0
breadnjam
breadnjam
This comment was minimized by the moderator on the site
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
Reply
Report
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations