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

*Article updated on November 30, 2010*

**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

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 […]### 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