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
Related posts:
Count date records between two dates in a range in excel
Count records between two dates in excel
Count unique records between two dates in excel
Count unique distinct records with a date and column criteria in excel 2007
Recommended categories
Thx! This is fantastic!
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,
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