How to Countif by date/month/year and date range in Excel?
For example, we have a membership roster with their birthdays, and now we are preparing to make birthday cards for these members. Before making the birthday cards, we have to count how many birthdays are in a specific month/year/date. Here I will guide you to Countif by date/month/year and date range with formulas in Excel with following methods:
- Countif by specific month/year with formulas in Excel
- Countif by specified year or date range in Excel
In this section, I will introduce some formulas to count birthdays by a certain month, year, or date range in Excel.
Countif by a certain month
Supposing you are going to count birthdays which are in a specific month of 8, you can enter below formula into a blank cell, and then press the Enter key.
In above formula, C3:C16 is the specified Date of Birth column you will count birthdays in, and G2 is the cell with the specific month number.
You can also apply this array formulas =SUM(IF(MONTH(B2:B15)=8,1)) (press Ctrl + Shift + Enter keys) to count birthdays by the specific month.
Countif by a certain year
If you need to count the birthdays by a certain year, says 1988, you can use one of below formulas as you need.
Note: The second formula an array formula. Please remember to press the Ctrl + Shift + Enter keys together after entering the formulas;
Countif by a certain date
If you need to count by a specific date (says 1992-8-16), please apply below formula, and press the Enter key.
Countif by a certain date range
If you need to count if later/earlier than a specific date (says 1990-1-1), you can apply the below formulas:
To count if between two specific dates (says between 1988-1-1 and 1998-1-1), please apply below formula:
|Formula is too complicated to remember? Save the formula as an Auto Text entry for reusing with only one click in future!
Read more… Free trial
If you have Kutools for Excel installed, you can apply its Select Specific Cells utility to count the number of occurrences by specified date, year, or date range in Excel easily.
1. Select the birthday column you will count in, and click Kutools > Select > Select Specific Cells. See screenshot:
2. In the opening Select Specific Cells dialog box, please do as above screenshot shown:
(1) In the Selection type section, please check one option as you need. In our case, we check the Cell option;
(2) In the Specific type section, select the Greater than or equal to from the first drop down list, and then type the first date of the specified date range in right box; next select the Less than or equal to from the second drop down list, and type the last date of the specified date range in right box, and then check And option;
(3) Click the Ok button.
3. Now a dialog box pops out and shows how many rows have been selected as below screenshot shown. Please click the OK button to close this dialog box.
(1) For counting the number of occurrences by specified year, just specified the date range from the first date of this year to the last date of this year, such as from 1/1/1990 to 12/31/1990.
(2) For counting the number of occurrences by specified date, such as 9/14/1985, just specify the settings in the Select Specific Cells dialog box as below screenshot shown:
You are guest
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 3 months agoi 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.
To post as a guest, your comment is unpublished.· 11 months ago@Emir 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.
To post as a guest, your comment is unpublished.· 11 months agoWhy we cant justselect whole column, instead of using this MONTH(C3:C16) ?
To post as a guest, your comment is unpublished.· 1 years ago@kelly001 Yes, they do - Excel rounds blank cells to January 0, 1900 - therefore when I'm trying to count January months over a set range that has not been filled in yet - I'm getting a higher number for January due to this.
To post as a guest, your comment is unpublished.· 2 years ago@Roxie 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
To post as a guest, your comment is unpublished.· 2 years agoWhat 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.
To post as a guest, your comment is unpublished.· 2 years ago@RHON Hi Rhon,
Could you describe more about the error? Does the error come out when converting December to 12, or when counting by “12”?
To post as a guest, your comment is unpublished.· 2 years ago@mary 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")
To post as a guest, your comment is unpublished.· 2 years agoJAN = 1
FEB = 2
DEC = 12 ? NOT CORRECT RESULT IN DECEMBER
To post as a guest, your comment is unpublished.· 2 years agoHow 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
To post as a guest, your comment is unpublished.· 2 years ago@Willie Pettigrew Hi Willie,
You can do as follows:
(1) Add a new column and round time data to half hour by the formula =MOD(INT(A2/(1/48))*(1/48),1) (A2 is the time cell)
(2) Select the data range including the new column, create a pivot table.
(3) In the PivotTable Fields pane, set date and the new half hour as Row fields, set the data you will sum or count as Value field.
(4) Turn off the grouping in the PivotTable.
To post as a guest, your comment is unpublished.· 2 years ago@IvTe Hi IvTe,
Do you need to count data meeting two conditions? You can use the COUNTIFS function.
To post as a guest, your comment is unpublished.· 2 years agorumus ini = SUMPRODUCT (1 * (YEAR (B2: B15) = 1988)) kalau datanya (range) sampe 20ribu ko ga bisa ya?
To post as a guest, your comment is unpublished.· 2 years agoThank you!
To post as a guest, your comment is unpublished.· 2 years agoI have a monthly report but need to count only daily data. I don't know to use Today option if formula is =COUNTIF(A1:A50,"RR")=TODAY(). Please help
To post as a guest, your comment is unpublished.· 3 years agoin Excel 2103 I have a long list of date and times, i am trying to calculate the number between each half hour, e.g. 8am and 8.30am on each date. Can you assist?
To post as a guest, your comment is unpublished.· 3 years ago@deepak Hi Deepak,
Just add the sheet name before referenced cells. For example your data is in Sheet1, and you want to count the sales in Feb, you can modify the formula as =SUMPRODUCT(1*(MONTH(Sheet1!B2:B15)=2))
To post as a guest, your comment is unpublished.· 3 years ago@kelly001 i have on sheet where in daily sales add in a single day
08-Feb-18 AMIT SHARMA
08-Feb-18 AVINASH KUMAR
08-Feb-18 SANJAY SEHGAL
but i need on second sheet
so pleaase tell me farumula for this counts total sales at other sheet
To post as a guest, your comment is unpublished.· 3 years ago@deepak =A2-A1+1
=END DATE - START DATE + 1
To post as a guest, your comment is unpublished.· 3 years ago@jimmy Hi jimmy,
Blank cells won’t affect the calculation result of this formula =SUMPRODUCT(1*(MONTH(B2:B15)=8))
To post as a guest, your comment is unpublished.· 3 years ago=SUMPRODUCT(1*(MONTH(B2:B15)=8))
I need to add 'exclude blank cells', please help.
To post as a guest, your comment is unpublished.· 4 years agoI WANT ANSWER 1 DAY( WITH EXAMPLE FORMULA)
COUNT BETWEEN :- 01/01/2004 TO 01/01/2004 = 1 DAY
WHICH FORMULA WILL APPLY
To post as a guest, your comment is unpublished.· 5 years agoI'm trying to create a table that counts the number of times a device has been used each month from another table which shows the date in cell a and then in cell b,c,d is a 1 if that device is being used on that date. So, on Sheet 2, is a table with Date (A1) Device 1 (B1) Device 2 (C1) Device 3 (D1). I would enter the date 01/01/16 in cell A1 and lets say Device 3 is being used so I would put a '1' in cell D1. on 02/01/16 device 2 is used so I would enter the date and put a '1' in cell C1. So on and so forth.
On Sheet 1, is a summary table which tells me how many times Device 1 has been used each month. So, in A2 I have the Month 'January', A3 'February' etc, in cell B2, Device 1, Cell C2, Device 2 etc. I want a total count. Is that possible?
To post as a guest, your comment is unpublished.· 5 years agoI am using =COUNTIFS(E2:E900,">"&"6/1/2015",E2:E900,"
To post as a guest, your comment is unpublished.· 5 years agoHi
I am trying to use a formula to count how many dates are passed a changing date, e.g TODAY()-330.
I need this to continue to change as it is for expiring training courses
What I have been using is =COUNTIFS(C3:C28,"