跳到主要内容

如何在Excel中按日期/月份/年份和日期范围进行计数?

作者:凯莉 最后修改时间:2020-04-26

例如,我们有一个成员名单以及他们的生日,现在我们正准备为这些成员制作生日贺卡。 在制作生日贺卡之前,我们必须计算出特定月份/年份/日期中有多少个生日。 在这里,我将通过以下方法使用Excel中的公式按日期/月/年和日期范围指导Countif:


用Excel中的公式按特定的月份/年份和日期范围计数

在本节中,我将介绍一些公式以在Excel中按特定月份,年份或日期范围对生日进行计数。

某月之前的Countif

假设您要计算特定8个月的生日,则可以在下面的公式中输入空白单元格,然后按 输入 键。

= SUMPRODUCT(1 *(MONTH(C3:C16)= G2))

笔记:

在上面的公式中,C3:C16是您要在其中计算生日的指定的“出生日期”列,而G2是具有特定月份数字的单元格。

您也可以应用此数组公式 = SUM(IF(MONTH(B2:B15)= 8,1)) (按Ctrl + Shift + Enter键)以按特定月份计算生日。

某年的Countif

如果您需要按某年计算生日,例如1988年,则可以根据需要使用以下公式之一。

= SUMPRODUCT(1 *(YEAR(C3:C16)= 1988))
= SUM(IF(YEAR(B2:B15)= 1988,1))

注意:第二个公式为数组公式。 请记住按 按Ctrl + 转移 + 输入 输入公式后将所有键放在一起;

在某个日期之前

如果需要按特定日期计数(例如1992-8-16),请应用以下公式,然后按Enter键。

=COUNTIF(B2:B15,"1992-8-16")

按特定日期范围计数

如果您需要计算是否晚于或早于特定日期(例如1990-1-1),则可以应用以下公式:

= COUNTIF(B2:B15,“>”&“ 1990-1-1”)
= COUNTIF(B2:B15,“ <”&“ 1990-1-1”)

要计算两个特定日期之间(例如1988-1-1和1998-1-1之间的时间),请使用以下公式:

=COUNTIFS(B2:B15,">"&"1988-1-1",B2:B15,"<"&"1998-1-1")

注意丝带 公式太难记了吗? 将公式另存为自动文本条目,以供日后再次使用!
阅读全文...     免费试用

轻松计算 Excel中的会计年度,半年,周号或星期几

Kutools for Excel 提供的数据透视表特殊时间分组功能能够添加辅助列来根据指定的日期列计算财政年度,半年,周数或星期几,并让您轻松计数,求和,或根据新数据透视表中的计算结果平均列。


Kutools for Excel - 使用 300 多种基本工具增强 Excel 功能。 享受全功能 30 天免费试用,无需信用卡! 立即行动吧!

在Excel中按指定的日期,年份或日期范围计数

如果您安装了 Kutools for Excel,您可以应用它 选择特定的单元格 实用程序可轻松在Excel中按指定的日期,年份或日期范围对出现次数进行计数。

Kutools for Excel- 包括 300 多个方便的 Excel 工具。 全功能免费试用 30 天,无需信用卡! 立即行动吧!

1。 选择要计入的生日列,然后单击 库工具 > 选择 > 选择特定的单元格。 看截图:

2。 在打开的“选择特定单元格”对话框中,请执行如上所示的屏幕截图:
(1)在 选择类型 部分,请根据需要检查一个选项。 在我们的情况下,我们检查 手机 选项;
(2)在 特定类型 部分,选择 大于或等于 从第一个下拉列表中,然后在右框中键入指定日期范围的第一个日期; 接下来选择 小于或等于 从第二个下拉列表中,在右框中键入指定日期范围的最后一个日期,然后检查 选项;
(3)点击 Ok 按钮。

3。 现在将弹出一个对话框,显示已选择了多少行,如下图所示。 请点击 OK 按钮关闭此对话框。

笔记:
(1)要计算指定年份的出现次数,只需指定从今年的第一天到今年的最后一个日期的日期范围,例如从 1/1/199012/31/1990.
(2)要计算指定日期(例如9/14/1985)的出现次数,只需在“选择特定单元格”对话框中指定设置,如下图所示:


演示:在Excel中按日期,工作日,月份,年份或日期范围计数


Kutools for Excel:超过 300 个方便的工具触手可及! 立即开始 30 天免费试用,没有任何功能限制。 立即下载!


相关文章:

最佳办公生产力工具

🤖 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 (33)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hi;

I need to determin how many times I need Know per week of the year how many time i nedd to see a patient , knowing de lenght of stay and the frequency of observation, 3 in the days ,
exemple :
patiente ex: date of admission : 8/3/2023 date of discharge : 31/8/2023 ; observations 3 in 3 days .
This comment was minimized by the moderator on the site
Hi, I need a formula to count how many times the country of El Salvador appears by Month (how many in Jan, how many if Feb, and how many in March)

1/10/2022 El Saldavor
1/11/2022 USA
1/12/2022 El Salvador
02/01/2022 El Salvador
02/06/2022 Mexico
02/05/2022 USA
03/03/2022 El Salvador
03/03/2022 El Savlador
03/03/2022 USA
This comment was minimized by the moderator on the site
Hi there,

You can add a helper column first with the formula =MONTH(data_cell) to convert the dates to corresponding month numbers. And then use a COUNTIFS formula to get the count of "El Salvador" for each month number.

For example, to get the number of El Salvador appears in January, use: =COUNTIFS(country_list,"El Salvador",helper-column,1)

Please see the picture below:
https://www.extendoffice.com/images/stories/comments/ljy-picture/count_items_by_month.png

Amanda
This comment was minimized by the moderator on the site
Προσπαθω να μετρήσω μια σιγκεκριμένη ημερομηνία αλλα τον τυπο που έχεται παραπάνω δεν το δέχεται το excel =COUNTIF(B2:B15,"1992-8-16")
This comment was minimized by the moderator on the site
Hi there,

Is it becase of the language your Excel version? Please check if COUNTIF should be converted to your language in your Excel verion. Also, when you say Excel does not accept the formula, did Excel show any errors or anything?

Amanda
This comment was minimized by the moderator on the site
01/03/2022 27/08/2022
02/02/2022 31/07/2022
01/04/2022 01/07/2022
01/04/2022 30/06/2022
01/04/2022 30/06/2022
30/04/2022 29/05/2022
20/04/2022 19/05/2022
15/04/2022 14/05/2022
15/04/2022 14/05/2022
15/04/2022 14/05/2022
04/04/2022 03/05/2022
01/02/2022 01/05/2022
13/04/2022 27/04/2022
16/04/2022 24/04/2022
04/04/2022 23/04/2022
15/04/2022 20/04/2022
21/03/2022 19/04/2022
11/04/2022 17/04/2022
05/04/2022 14/04/2022
15/03/2022 13/04/2022
15/03/2022 13/04/2022
15/03/2022 13/04/2022
15/03/2022 13/04/2022
15/03/2022 13/04/2022
15/03/2022 13/04/2022
15/03/2022 13/04/2022
15/03/2022 13/04/2022
15/03/2022 13/04/2022
22/03/2022 10/04/2022
27/03/2022 05/04/2022
29/03/2022 04/04/2022
03/01/2022 03/04/2022
03/01/2022 03/04/2022
03/01/2022 03/04/2022
25/03/2022 03/04/2022

Como contar apenas os dias do mês 04 nesses intervalos?
This comment was minimized by the moderator on the site
Hi, are you trying to count the number of days in April?
If yes, you should first select the two columns, then go to Kutools tab, in the Ranges & Cells group, click To Actual. Now, in the Editing group, click Select, and then click Select Specific Cells.
In the pop-up dialog box, select Cell option under the Selction Type; Under Specific type, select Contains, and type 4/2022 in the corresponding box. Click OK. Now, it will tell you how many days of April are there. Please see screenshot.
This comment was minimized by the moderator on the site
i need report weekly wise like a 7th 14th 21st 28th count only.other days are no need for every month i tried many pivot but i can't find out please help me.
This comment was minimized by the moderator on the site
Why we cant justselect whole column, instead of using this MONTH(C3:C16) ?
This comment was minimized by the moderator on the site
In the formula =SUMPRODUCT(1*(MONTH(C3:C16)=G2)), G2 is the specified month number, says 4. If we replace MONTH(C3:C16) with the whole columns (or C3:C16), there is no value that equal to the value 4, therefore we cannot get the count result.
This comment was minimized by the moderator on the site
What would you do if you have 1 column of events within a date range and need to count if those events had a date in another column?

Example: I have column B as the event dates which vary each month. Column D has the date they came into a consultation. I'm trying to count how many people from that specific event for a date range came to a consultation for any date.
This comment was minimized by the moderator on the site
Hi Roxie,
You should try the Compare cells feature, which can compare two columns of cells, find out, and highlight the exactly same cells between them or the differences. https://www.extendoffice.com/product/kutools-for-excel/excel-compare-two-cells-of-equal.html
This comment was minimized by the moderator on the site
JAN = 1
FEB = 2
..
..
DEC = 12 ? NOT CORRECT RESULT IN DECEMBER
This comment was minimized by the moderator on the site
Hi Rhon,
Could you describe more about the error? Does the error come out when converting December to 12, or when counting by “12”?
This comment was minimized by the moderator on the site
How can I count a cell on a specific day of the week. For example, I want to find a number of something on the first Sunday of the month
This comment was minimized by the moderator on the site
Hi mary,
In your case, you should count by the specified date. For example, count by the first Sunday of Jan, 2019 (in other words 2019/1/6), you can apply the formula =COUNTIF(E1:E16,"2019/1/6")
This comment was minimized by the moderator on the site
rumus ini = SUMPRODUCT (1 * (YEAR (B2: B15) = 1988)) kalau datanya (range) sampe 20ribu ko ga bisa ya?
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations