## Count records between two dates with multiple parameters

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

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. What's on this […]

This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

Count cells between a given value

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

Count unique distinct values in two columns

Question: I have values in two not adjacent columns. I want to know how many unique distinct values there are […]

Question: I am trying to create an excel spreadsheet that has a date range.Example: Cell A1 1/4/2009-1/10/2009 Cell B1 1/11/2009-1/17/2009 […]

Find latest date based on a condition

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]

Formula for matching a date within a date range

Table of contents Match a date when a date range is entered in a single cell Match a date when […]

The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the […]

Highlight overlapping date ranges using conditional formatting

The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]

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

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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