跳到主要內容

如何在Excel中按日期/月份/年份和日期範圍進行計數?

例如,我們有一個成員名單以及他們的生日,現在我們正準備為這些成員製作生日賀卡。 在製作生日賀卡之前,我們必須計算出特定月份/年份/日期中有多少個生日。 在這裡,我將通過以下方法使用Excel中的公式按日期/月/年和日期範圍指導Countif:


用Excel中的公式按特定的月份/年份和日期範圍計數

在本節中,我將介紹一些公式以在Excel中按特定月份,年份或日期範圍對生日進行計數。

某月之前的Countif

假設您要計算特定8個月的生日,則可以在下面的公式中輸入空白單元格,然後按 Enter 鍵。

= 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 + 轉移 + Enter 輸入公式後將所有鍵放在一起;

在某個日期之前

如果需要按特定日期計數(例如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提供的數據透視表特殊時間分組功能可以添加一個助手列,以根據指定的日期列計算會計年度,半年,週數或星期幾,並讓您輕鬆地計算,求和,或基於新列數據透視表中計算結果的平均列。


Excel的Kutools - 使用 300 多種基本工具增強 Excel 功能。 享受全功能 30 天免費試用,無需信用卡! 立即行動吧!

在Excel中按指定的日期,年份或日期範圍計數

如果您已安裝Kutools for Excel,則可以應用它 選擇特定的單元格 實用程序可輕鬆在Excel中按指定的日期,年份或日期範圍對出現次數進行計數。

Excel的Kutools- 包含 300 多個方便的 Excel 工具。 全功能免費試用 30 天,無需信用卡! 立即行動吧!

1。 選擇要計入的生日列,然後單擊 庫工具 > 選擇 > 選擇特定的單元格。 看截圖:

2。 在打開的“選擇特定單元格”對話框中,請執行如上所示的屏幕截圖:
(1)在 選擇類型 部分,請根據需要檢查一個選項。 在我們的情況下,我們檢查 細胞 選項;
(2)在 特定類型 部分,選擇 大於或等於 從第一個下拉列表中,然後在右框中鍵入指定日期範圍的第一個日期; 接下來選擇 小於或等於 從第二個下拉列表中,在右框中鍵入指定日期範圍的最後一個日期,然後檢查 選項;
(3)點擊 Ok 按鈕。

3。 現在將彈出一個對話框,顯示已選擇了多少行,如下圖所示。 請點擊 OK 按鈕關閉此對話框。

筆記:
(1)要計算指定年份的出現次數,只需指定從今年的第一天到今年的最後一個日期的日期範圍,例如從 1/1/199012/31/1990.
(2)要計算指定日期(例如9/14/1985)的出現次數,只需在“選擇特定單元格”對話框中指定設置,如下圖所示:


演示:在Excel中按日期,工作日,月份,年份或日期範圍計數


Excel的Kutools:超過 300 個方便的工具觸手可及! 立即開始 30 天免費試用,沒有任何功能限制。 現在就下載!


相關文章:

最佳辦公生產力工具

🤖 Kutools 人工智慧助手:基於以下內容徹底改變數據分析: 智慧執行   |  生成代碼  |  建立自訂公式  |  分析數據並產生圖表  |  呼叫 Kutools 函數...
熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup    多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧    工作簿和工作表管理器   |  資源庫 (自動文字)   |  日期選擇器   |  合併工作表   |  加密/解密單元格    按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符,...)   |   50+ 圖表 類型 (甘特圖,...)   |   40+ 實用 公式 (根據生日計算年齡,...)   |   19 插入 工具 (插入二維碼, 從路徑插入圖片,...)   |   12 轉化 工具 (數字到單詞, 貨幣兌換,...)   |   7 合併與拆分 工具 (高級合併行, 分裂細胞,...)   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

產品描述


Office選項卡為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
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations