## Count records between two dates with multiple parameters in excel

**Sam asks in this post: **Count records between two dates in excel

Any chance this would work with multiple parameters.

I need to count how many times JA appears in a given period 1-Sep-10 and 10-Sep-10

A B

1 JA 2-Sep-10

2 SH 11-Sep-10

3 JA 3-Sep-10

I can't seem to link CountIf(A1:A3,"JA") and the Sum(If) part

any help greatly appreciated.

**Answer:**

**Count records between two dates and a criterion**

**Formula in E2:**

=SUMPRODUCT(--($B$1:$B$9<=$E$2), --($B$1:$B$9>=$E$1), --($A$1:$A$9=$E$3)) + ENTER

**Count records between two dates and multiple criteria**

** **

**Formula in E4:**

=SUMPRODUCT(--($B$1:$B$9<=$E$2), --($B$1:$B$9>=$E$1), --(COUNTIF(**$G$1:$G$2**, $A$1:$A$9))) + ENTER

Adjust bolded range ($G$1:$G$2) to add more criteria.

### Download excel sample file for this tutorial

Count records between two dates and multiple criteria.xls

(Excel 97-2003 Workbook *.xls)

### Functions in this article:

**SUMPRODUCT(**array1, array2, **)**

Returns the sum of the products of the corresponding ranges or arrays

**COUNTIF(**range,criteria**)**

Counts the number of cells within a range that meet the given condition

### Category: Count values

Count unique distinct values that meet multiple criteria

This post demonstrates how to build an array formula that counts unique distinct values based on criteria. Tip! I highly […]Comments(93) Filed in category: Count unique distinct values, Count values, Excel

Count unique distinct values in a column

Question: I have a list of values (A1:A6), how do I count unique distinct values? Answer: First, let me explain […]Comments(26) Filed in category: Count unique distinct values, Count values, Excel

Counting conditionally formatted cells (vba)

UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you […]Comments(26) Filed in category: Conditional formatting, Count values, Excel

Count number of times a string exist in multiple cells using excel formula

Question: How do I find the number of occurances a word exists in a range of cells? It does not […]Comments(19) Filed in category: Count text values, Count values, Excel

Excel: List intervals between two values

jeyner asks: I need to count in a list the interval between the same value. Example list, 1-2-3-1-4-5-1-6-7-8-9-7-8-1 So the […]Comments(16) Filed in category: Count values, Excel, Range

### Category: Dates

Create a date range using excel formula

Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/09-1/10/09 Cell B1 […]Comments(142) Filed in category: Dates, Excel

Lookup a value and find max date

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]Comments(83) Filed in category: Dates, Excel

Formula for matching a date within a date range in excel

Table of contents Match a date when a date range is entered in a single cell Match a date when […]Comments(48) Filed in category: Dates, Excel

Finding the nearest date in a range of dates using excel formula

Array formula in E3: =INDEX(A1:A10, MATCH(MIN(ABS(A1:A10-$E$1)), ABS(A1:A10-$E$1), 0)) Recommended articles: How to enter an array formula Select cell E3 Type […]Comments(42) Filed in category: Dates, Excel

Highlight overlapping date ranges using conditional formatting

How to highlight overlapping date ranges Click "Home" tab Click "Conditional Formatting" button Click "New Rule.." Click "Use a formula […]Comments(31) Filed in category: Excel, Overlapping

### 14 Responses to “Count records between two dates with multiple parameters in excel”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Thx! This is fantastic!

1-Jan-15 2-Jan-15 3-Jan-15 4-Jan-15 5-Jan-15 6-Jan-15 2-Feb-15 3-Feb-15 4-Feb-15

6 6 6 6 6 6 6 6 6

1 1 1 1 1 1 1 1 1

P P P P P P P P P

Q Q Q Q Q Q Q Q Q

P P P P P P P P P

P P P P P P P P P

P P P P P P P P P

P P P P P P P P P

P P P P P P P P P

this my table in that i want to count p based on start and end date.

I am trying to create a a single report from 12 worksheets in a workbook. Each tab has data in the same format, and represents data for a different country. How do I combine and summarize this , so that data for each country is summarised in a different column.

Shaz,

Excel Pivot Table Tutorial -- Multiple Consolidation Ranges

Nesta tabela gotaria de contar ou somar entre datas colocando eu duas data dentro de uma célula

somar os valores entre 20-02-2010 e 03-03-2010

B C

1 81 20-01-2010

2 73 20-01-2010

3 82 17-02-2010

4 79 24-02-2010

5 59 03-03-2010

6 28 03-03-2010

7 78 03-03-2010

HI Oscar,

i need to count p from one year data.all data's are mentioned in sheet 1.

in sheet

if D is start date and F is end date. in column G

i want to know total count of the "p" for the period.

pls help me on this issue.

regards,

kavi

Hi Oscar,

In your examples above, you use sumproduct to look between a certain date range. You then add in a countif to count criteria that falls within that date range. Is it possible to add a second set of criteria to count by?

So in my mind something like: =SUMPRODUCT(--($B$1:$B$9=$E$1), --(COUNTIF($G$1:$G$2, $A$1:$A$9), --(COUNTIF($H$1:$H$2, $C$1:$C$9)))) + ENTER

Where H1:H2 contains more criteria and C1:C9 is a complete new column of data?

Rob,

Yes! I tried your example and formula and it works here. I guess you get some kind of error? What are the new criteria H1:H2 and what are the cells in C1:C9 containing?

Hi Oscar,

In looking at the formula I provided, I left out the second date period so should have read:

=SUMPRODUCT(--($B$1:$B$9<=$E$2),(--($B$1:$B$9=$E$1), --(COUNTIF($G$1:$G$2, $A$1:$A$9), --(COUNTIF($H$1:$H$2, $C$1:$C$9)))) + ENTER

The new criteria in H1:H2 are country names and C1:C9 contains a list of different countries. So I want to look at a list of entries in a system between a certain date period that are firstly at a specific status (G1:G2) and then in specific countries (C1:C2).

Is this possible?

Apologies for the mistake in my previous post and thanks for any help you can provide.

Oscar,

=SUMPRODUCT(--($B$1:$B$9<=$E$2),(--($B$1:$B$9=$E$1), --(COUNTIF($G$1:$G$2, $A$1:$A$9), --(COUNTIF($H$1:$H$2, $C$1:$C$9)))) + ENTER

Error in second sumproduct formla - need to remove the bracket (

Thanks

re: Count records between two dates and a criterion

based on the example, i was looking for 1 date and 1 criterion. i slightly modify the formula to

=SUMPRODUCT(--($B$1:$B$9=$E$2), --($A$1:$A$9=$E$3)) + ENTER

[assuming E2 = 9-2-2010]

the result would be 1 (one 'JA' found on 9-2-2010 date)

but this is summation of records found on 1 date with 1 criterion. It will not work if there is *multiple* 'JA' criterion exist on the same date because SUMPRODUCT summed up the records found.

I'm curious to know...

1) What if I want to know the UNIQUE DISTINCT records found on 1 date with 1 criterion?

2) Working on >100k rows of data, this formula literally slows down Excel (heavy calculation and recalculations). Is there an alternative to speed it up? UDF? array formula?

thanks! ;)

davidlim,

read this post: Count unique distinct records between two dates and a condition in excel 2007

Oscar,

You are now getting me closer to the answer of my problem but I'm still not there. My issue is that I get the date in this format:

7/23/2011 7:00:00 AM

I am trying to count how many entries are between date and time. So I have a shift that starts at 6:30:00 PM and leaves at 7:00:00 AM the next morning. I have tried to convert that to a value to no avail. I have the cells formatted correctly and tried your other formula =SUM(IF(($A$2:$A$10$D$1),1,0)) + CTRL + SHIFT + ENTER but that returned all records. I am starting to think that Excel (I'm using 2010) cannot differentiate between the date and time. Any ideas would be greatly appreciated.

Thanks!

Toolman,

read this post:

Count entries between date and time criteria in excel